Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Returning row # using match or index of repeated text in a complex table
Confusing subject I know.
I have to process data that looks like this: A1: GE A2: (empty) A3: Q104 A4: Q204 A5: Q304 A6: (empty) A7: Ford A8: Q104 A9: Q304 A10: (empty) A11: (empty) A12: AOL etc.. Given a company name (GE, Ford, AOL, et...) and a quarter (Q104, Q204, etc..), I need to be able to return the row number for that quarter for that company. I can't use a simple match function because the quarters are repeated - for example, Q104 shows up in both GE and Ford. For Ford Q104 I need to return 8, while for GE Q304, I need to return 5. Constraints: 1)The quarters will be in sequential order, earliest to latest. 2)Some companies are missing quarters, and I won't know ahead of time how many companies each quarter has. 3) The data will be replaced frequently, sometimes companies will just have a few quarters, sometimes they will go back 10 years 4) Some companies will be missing quarters. If this happens, then the next available quarter will be in that row (look at row 9 above) 5) Because of (3), I can't use names - I can't make new names for each company whenever the user updates the sheet. 6) I want to do this with spreadsheet functions only - no macros (stupid company policy). I just need to return the row number, using only worksheet functions - no macros. Please help!!! Phil |
#2
|
|||
|
|||
Returning row # using match or index of repeated text in a complex table
"General" wrote in news:1129835807.668986.77450
@g43g2000cwa.googlegroups.com: Confusing subject I know. I have to process data that looks like this: A1: GE A2: (empty) A3: Q104 A4: Q204 A5: Q304 A6: (empty) A7: Ford A8: Q104 A9: Q304 A10: (empty) A11: (empty) A12: AOL etc.. Given a company name (GE, Ford, AOL, et...) and a quarter (Q104, Q204, etc..), I need to be able to return the row number for that quarter for that company. I can't use a simple match function because the quarters are repeated - for example, Q104 shows up in both GE and Ford. For Ford Q104 I need to return 8, while for GE Q304, I need to return 5. Constraints: 1)The quarters will be in sequential order, earliest to latest. 2)Some companies are missing quarters, and I won't know ahead of time how many companies each quarter has. 3) The data will be replaced frequently, sometimes companies will just have a few quarters, sometimes they will go back 10 years 4) Some companies will be missing quarters. If this happens, then the next available quarter will be in that row (look at row 9 above) 5) Because of (3), I can't use names - I can't make new names for each company whenever the user updates the sheet. 6) I want to do this with spreadsheet functions only - no macros (stupid company policy). I just need to return the row number, using only worksheet functions - no macros. Please help!!! Phil Phil, It could probably be done if you knew the company order: Search for the company in question to get the first row Search for the next company in the list to get the last row Then search for the quarter between these rows However, I would suggest that you change the structure of the data. Company and Quarter should be separate columns: GE Q104 GE Q204 GE Q204 .. .. .. If D1="GE" and D2="Q204" then you could then use something like: {=MATCH(D1&D2,A1:A10&B1:B10,0)} to return the position. Note that this is an array formula, use Control+Shift+Enter instead of just Enter when typing the formula. Excel will add the brackets. Is there anything useful in the empty rows? HTH, RP |
#3
|
|||
|
|||
Returning row # using match or index of repeated text in a complex table
Thanks RP, but that won't quite work. Reason is that the company name
and the quarter are in the same column, not next to each other. This spreadsheet could be hundreds of companies long, and manually copy & pasting the company name in a neighboring column would take way too long. I need the whole entire thing to be automated. Thanks anyway, please let me know if you have any ideas. Phil |
#4
|
|||
|
|||
Returning row # using match or index of repeated text in a complex table
"General" wrote in news:1129837940.963257.5400
@o13g2000cwo.googlegroups.com: Thanks RP, but that won't quite work. Reason is that the company name and the quarter are in the same column, not next to each other. This spreadsheet could be hundreds of companies long, and manually copy & pasting the company name in a neighboring column would take way too long. I need the whole entire thing to be automated. Thanks anyway, please let me know if you have any ideas. Phil Phil, How is this data produced? I did not mean to imply any manual manipulation of the data on your part. Ideally the creator of the data could do this for you. If not, we could use some simple formulas or a macro¹ to improve the structure. Does anyone else depend on this data? in this format? Is there anything useful in the empty rows? -RP ¹policy schmolicy, have whoever doesn't like VBA fix the data then |
#5
|
|||
|
|||
Returning row # using match or index of repeated text in a complex table
Hi RP,
The data is spit out by an outside program, and the format can't be changed. Use of VB is not an option. Thx, Phil |
#6
|
|||
|
|||
Returning row # using match or index of repeated text in a complex table
Hi RP,
The data is spit out by an outside program, and the format can't be changed. Use of VB is not an option. Thx, Phil |
#7
|
|||
|
|||
Returning row # using match or index of repeated text in a complex table
"General" wrote in news:1129841369.520961.179420
@g49g2000cwa.googlegroups.com: Hi RP, The data is spit out by an outside program, and the format can't be changed. Use of VB is not an option. Thx, Phil Phil, Insert a row so that the data starts in A2. Put the following formula in the second row of an empty column (change B1 to reflect the actual column used) and then copy it down. =IF(OR(LEFT(A2,1)="Q",A2=""),B1,A2) Now you can use the two column match. Actually, you could append the contents of A2 after the IF function. Then you could just use a regular MATCH and avoid the array formula. This formula assumes any non-blank cell that does not begin with a 'Q' is a company name. The first argument would need to be refined if this is not the case (i.e. you have companies that start with 'Q'). This should get you going though. HTH, RP |
#8
|
|||
|
|||
Returning row # using match or index of repeated text in a complex table
You can easily create another column with the company name using the
formula below, which assumes the following: Your list is in column A starting at A1. No company name ends in a number (0-9) All quarter identifications end in a number. Put the following formula in B2 down: =IF(ISERROR(VALUE(RIGHT(A2))),A2,B1) This should put the company name in column B. Now use RP's formula to find the match for your Company and quarter. In this case, assuming your search company is in C1 and Quarter in D1, you would write =MATCH(C1&D1,B1:Bnn&A1:Ann,0), where nn is the ending row of your list - entered with Ctl+Shift+Enter, since it is an array formula. If some company name ends in a number, then you will need a different formula to put the company name in another column, but I will wait for your response before suggesting what that might be. Good luck. General wrote: Thanks RP, but that won't quite work. Reason is that the company name and the quarter are in the same column, not next to each other. This spreadsheet could be hundreds of companies long, and manually copy & pasting the company name in a neighboring column would take way too long. I need the whole entire thing to be automated. Thanks anyway, please let me know if you have any ideas. Phil |
#9
|
|||
|
|||
Returning row # using match or index of repeated text in a complex table
General wrote...
.... A1: GE A2: (empty) A3: Q104 A4: Q204 A5: Q304 A6: (empty) A7: Ford A8: Q104 A9: Q304 A10: (empty) A11: (empty) A12: AOL .... Given a company name (GE, Ford, AOL, et...) and a quarter (Q104, Q204, etc..), I need to be able to return the row number for that quarter for that company. .... If the list begins in A1 (so in row 1), and company name were entered in a cell named COID and quarter in a cell named QTR, enter Q1, Q2, Q3 and Q4 in a single column range just below a blank cell (e.g., X2:X5 with X1 blank) and name that range - INCLUDING the blank cell - QP, then try the array formula =MATCH(QTR,OFFSET(A1:A100,MATCH(COID,A1:A100,0),0, MATCH(0, COUNTIF(QP,LEFT(OFFSET(A1:A100,MATCH(COID,A1:A100, 0),0),2)), 0)-1,1),0)+MATCH(COID,A1:A100,0) |
#10
|
|||
|
|||
Returning row # using match or index of repeated text in a complex table
An alternative simple formula, for putting the company name in column
B, which treats everything that is not in the format "Qnnn" as a company name is =IF(AND(LEFT(A2)="Q",ISNUMBER(VALUE(RIGHT(A2,3)))) ,B1,A2) |
#11
|
|||
|
|||
Returning row # using match or index of repeated text in a complex table
Beautiful Harlan, beautiful!
But that company column may still be useful to him for whatever else he wants to do with that data ... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index Match With 3 Variables | Excel Worksheet Functions | |||
Counting Repeated text or duplicates in a list | Excel Discussion (Misc queries) | |||
Find a match that;s not exact | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions | |||
returning a text cell based on a number cell | Excel Worksheet Functions |