I have a sheet that lets the user enter a category and then a sub heading, I need the data to be displayed under each category heading in alpha/numeric order as each new entry is made into the list.
Please see the attached sheet for details. Book3.xlsx‎
Calculation Order. As the title states.
View 2 Replies View RelatedIn Cell A1 i have a text string of "ABS9E8C2D" i want something is cell B1that will arrange the string in Alphabetical order for Letters, then increasing order for numbers "ABCDES289".
View 7 Replies View RelatedI am trying to number rows based on criteria. when the value changes add a 1. This is what I need to have happen
Apple - 1
Apple - 1
Apple - 1
Orange - 2
Orange - 2
Plum - 3
Plum - 3
I can get an if statement to number everything 1.
i have two date lists
list 1 dates a1:a100
list 2 dates b2:b100
i want to extract the date from the two lists mentioned above in on list start from c2 taking in consideration the dates to be in sorting from A-Z when it extracted in the one list
i need formula not a macro
I’m getting data from query. My data has fields: Item No, Customer ID & Sales rep.
I need to find out for each Item number which customer put order and who was the sales rep. (Excel 2002). see file attached.
I need to put the order in the second column of the item that is in the first column.
In the last line item Computer is recorded for the third time.
MATERIALSSEQUENCEComputer1Computer2Mouse1Mouse2Computer3
I need to automatically populate a column with an alphanumeric MFR0001 to MFR9999 and am currently using the formula below to add 1 to the start point (A1)...
A1=MFR0001
Formula in A2 =LEFT(C1,3)&(RIGHT(C1,4)+1)
This works perfectly if I start at MFR1000, but breaks and drops the 000 if I use it on the number I need to start with (MFR0001).
Here I would like to get your valued guidance for the usage of "IF" formula. I am having two sheets one is new XL-1 and other is old XL-2. I want to have a item number from the old file corresponding to the same item descprition in the new file. I am attaching the snap shots of two files which is more explanatory than describing in words here.
View 2 Replies View RelatedI've found several threads describing how to make a chart title dynamic by entering a cell reference into the formula bar with the chart title selected. I am wondering if anyone knows if it is possible to have VBA "code" a formula into a chart title that would be a concatenation of several strings. Ideally, I'd like to specify part of the title from a variable, and another part from a user-specified range, as in the following snippet from a module of mine:
ChartSubtitleText = Sheets("Sheet1").Range("A5")
ActiveChart.ChartTitle.Select
Selection.Characters.Text = ChartTitleText & Chr(10) & ChartSubtitleText
where ChartTitleText is a string variable that is passed to the Sub. This works fine and correctly builds the title block during execution of the macro, but I'd like to modify the capability to allow the user to enter a new value in Sheet1!A5 and have all of the charts update dynamically without re-executing the macro. Is there a way to have VBA create the chart and build the Chart Title with a formula that will allow for dynamic updating? I've tried having it enter the following line into the formula bar, but get errors: =CONCATENATE(ChartTitleText, CHAR(10), Sheet1!A5)
I have one worksheet which comes out of an online application (for the purposes of this example I will call it "Online Sheet"). The column headings in this sheets are always named the same but could theoretically appear in any column address. For example, the column header "Completed" could appear in column "X", or "AT", or "ZZ".
On a second sheet (called "Code Sheet") I have to unscramble all of this data into a standardize layout. Each row value has a unique ID which appears on both the "Code Sheet" and the "Online Sheet". However, in the "online sheet" the unique ID could also appear in any column. Like the "Completed" column this column also has a unique column heading ("Unique ID").
Using Match I can calculate the column number in which the "Completed" column appears in this instance of the online data. For example, "Completed" = Column "25". However, I now need to use some lookup function on this column based on the row in which my "Unique ID" appears of the "Online Sheet" (which could of course be in any column in the "Online Sheet").
For example, if "Completed" is in column "25" and the "Unique ID" which I am referencing on the "Code Sheet" appears on row 14 in the "Online Sheet", then the lookup formula must return the value of column 25 row 14 on my "Code Sheet".
I'm using the formula '=LEFT(A2,4)+0 to shorten a numbered line of text.Is there a way to adopt that formula to do the same thing to an alphanumeric line of text (i.e. 80A0267AA1 to read 80A0267) or even better substitute a "-" in place of the "A" (i.e. 80A0267AA1 to read 80-0267)? I know this is really picky, but I figure it's worth a shot.
View 10 Replies View RelatedI have another question regarding extracting Alphabet from the alpha Numeric String:
Column A
POT470G12B
PBY100G12
PCLN6400S12
PCX.US10X12
AAPL6000G12A
BP380S12A
BP390S12
The output i want from the string is :
Column A
Column B
POT470G12B
G
PBY100G12
G
[Code] ........
I have a data set of students results and I have been using countif to pickup similar grades eg, C1, C2 and C3 all as C. However I need to subtotal filter against teachers and the sumproduct won't let me use C* as a wildcard to pick up the three types of C. How I can subtoal countif multiple criteria.
View 3 Replies View RelatedThe attached file Countifs issue.xlsx shows my attempt to construct a "CountIfs" formula that finds the one record out of ten that matches the following criteria:
a) Col A has 10 IDs. 3 of them are between 40 - 49, and another 2 are alphanumeric. This formula SHOULD identify these 5:
=COUNTIFS(A2:A11,">39",A2:A11,"<50",A2:A11,"4AB",A2:A11,"40C")
Doesn't work, It finds the 3 numerics but returns "0" when the two alpha-numerics are added.
b) Col B has a list of dates. 5 of the 10 are this month. This formula SHOULD identify them:
=COUNTIFS(B2:B11,(EOMONTH(NOW(),-1)+1),B2:B11,(EOMONTH(TODAY(),0)))
Doesn't work. It returns a value of "0".
c) Col C shows the Status for each record. 5 are "A" and 5 are "B". The following formula DOES find all the "B"s
=COUNTIF(C2:C11,"B")
But obviously I need the full formula to find the one record out of the ten that meets all the criteria.
in (a) and (b) and enable me to combine all three sections.
Excel Userform
VB:
'enables user to click [U]highlight and select[/U] an item in ListBox1 and ListBox2 item (same row in index) is also [U]highlighted[/U] (highlighted only not selected)
Private Sub ListBox1_Click()
ListBox2.ListIndex = ListBox1.ListIndex
End Sub
Question: Is it also possible to enable a user to click to select an item in ListBox1 and ListBox2 item is also selected simultaneously (same row in index). Is there excel vb code to do this?
I think the code may be along the lines of the ListBox SelectedIndex property. What would be the Excel VB code equivilant for the ListBox SelectedIndex property, if so?
When copying the formula =SUMIFS(G3:G103,I3:I103,"3") how do I modify it so that only the "3" rolls to the next integer and the rest of the formula remains constant? In other words the 3 would roll to a 4 when copied down a column and the rest of the formula does not change?
View 4 Replies View RelatedHere is my current formula:
=IF(ISERROR(INDEX(Sheet3!$D$5:$D$200,MATCH(E5,IF(Sheet3!$C$5:$C$200=C5,Sheet3!$A$5:$A$200),0))),"0",INDEX(Sheet3!$D$5:$D$200,MATCH(E5,IF(Sheet3!$C$5:$C$200=C5,Sheet3!$A$5:$A$200),0)))
I would like to add one more item to match. I need to match column H in my current sheet, to that of column E in sheet 3. Just not sure how to go about it.
In the attached sheet i want one more column that will populate week no for each item .. using excel formula. like for item A if 1st date if 1/6 then value will be "W" and if it is 8/6 that is greater than w then the value is "W +1" and we can do it otherwise.. Formula to populate this.
sortbyDates.xlsx‎
Col A contains SS#'s and Col B contains names. Quite often there are duplicates and sometimes triplicates because they may be sending payments for multiple providers. We charge one fee per client (unique SS#), regardless of the number of times they may be on the spreadsheet.
I can get a grand total by summing the unique items by using this formula:
=SUM(IF(LEN(A2:A4970),1/COUNTIF(A2:A4970,A2:A4970)))
and then doing a CTRL-SHIFT-ENTER to put brackets around it so it will "take."
However I now need to show the fee by each unique name. Right now I am inserting Col C, typing in 3.00 into C2 and copying on down, but then have to manually remove the amounts in the rows which contain duplicate ss#. Long painful process. How can I put a formula in Col c so that it will only show fee one time by the first unique ss#?
i need a formula in Cell B2, that will look in raw data and then look for the team in criteria (named range) then return the number of item for each manager....
View 9 Replies View RelatedI have attached a few rows of data:
Current PositionCurrent Store FT MGR 212Proposed PositionProposed Store PT MGR 212
In the Curent Position Cell and Proposed Position Cell there is a list of the following options: FT MGR/PT MGR/FT ASST MGR/PT ASST MGR
Is there an IF, THEN formula that I can use to tell whenever the FT equivalent or PT Equivalent shows, AND the current and proposed store is the same, then "NO ACTION REQUIRED"
I was trying to send this as an attachment but I don't see that option
numberdesc
1_______yellow
1_______yellow
1_______blue
2_______purple
2_______purple
3_______green
3_______orange
4_______black
I need some way that can identify when the item in the description column doesnt match the first item of the same number- for example, here the 1-blue and 3-orange would be flagged because they should match the 1-yellow and 3-green.
I need to do this on a much larger scale (approximately 20,000 data points), so I wanted to create a formula or macro that could do this for me.. I thought making a reference page with would work but I keep getting an error.. I haven't done VBA in a while, so I may have syntax errors.
If Range("A2:A9").Sheets("Sheet1") = Range("A2:A6").Sheets("Ref") And Range("B2:B9").Sheets("Sheet1") = Range("B2:B6").Sheets("Ref") Then
Range("C2:C9").Sheets("Sheet1") = "x"
End If
I am getting close to finishing the drop down menu capability when filling in column L in tab Transactions. However, there is a snag. When I enter part of account say "fin" (the important part here is that the part of the word should not be the beginning of the account name) - then I select an account from the menu - but it does not stay in the cell if the part of the name is the beginning of the account name - all is fine.
View 4 Replies View RelatedLet's say I have a column A with a list of dates like this:
02/01/14
02/08/14
01/01/14
06/15/14
04/13/14
05/12/14
01/13/14
04/01/14
06/01/14
I'd like to then write a formula in column B that tells me what number order the corresponding date in column A would be if column A were sorted in chronological order, broken out by month.
So the results would look like this:
02/01/14 0201
02/08/14 0202
01/01/14 0101
06/15/14 0602
04/13/14 0402
05/12/14 0501
01/13/14 0102
04/01/14 0401
06/01/14 0601
So the first two numbers of column B are simply the month, and the last two are telling you whether it's the first, second, third etc chronological date of that month, that is found in the entire list.
If column B needs to be a concatenation of a couple columns of formulas to get to that result, that's fine.
The whole idea behind this is to assume that the data in column A can not/will not be sorted by Excel.
I have "bashed into shape" a formula that involves 2 sets of parenthesis. After progressing beyond "#REF! or #NAME errors, Excel's automated Help suggested to add a comma, and the formula functions as wanted, I thought I was "Smart".
Later, when saving the formula to my `Formula Workbook', "Order of Operations" crossed my mind. - I think this formula is backwards, but `Excel Help' only suggested the addition of a comma? Maybe the all inclusive parenthes allowed the order to be understood?
The Formula: =AVERAGE((E56:AH56),COUNTIF(E56:AH56,">0"))
Q: Shouldn't the COUNTIF occur before the AVERAGE? Did the extra Parenthis automate the order of Operation within the Parenthis?
I have created a drop down list for items we quote on. each item has a different formula depending on the variables on sheet one Sheet1. I have listed the formulas in the column next to the drop down list for each item, but our now need the main quote to put in the formula in another cell when the item is chosen from the drop down list.
View 2 Replies View RelatedMy problem in the given excel file
1. Sheet1; how to Add "B000" in the List A or "B00" if the digits and 5 in the list
2. Sheet2; how to remove "B000" or "B00" from the list
3. Sheet3; how to highlight difference or find out the difference between to lists
Update_List.xlsx
In worksheet 1 I have a list of employee numbers (column A) and and names (Column B)and in worksheet 2 I have a list of employee numbers (column A) and their payroll contracts (Column B). 1 employee can have multiple contracts).
What I'm trying to achieve in workshet 1 is to have a dropdown list ( Column C) which returns just the contracts that relate to the employee number in column A.
I have two lists and I would like a formula which only returns unique values - similar to an advanced formula. Is this possible?
In the attached example my data is in columns C and D and I would like a formula in column E if possible. I need it to ignore the capitals. E.g. BLACKBURN and Blackburn appears in each list but I would only want one instance pulled through in column E.
Currently in column E I have the desired result.