Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup of specific text, then in consecutive rows
I'd like to have vlookup return a name if a cell contains the word relief.
And also in consecutive rows. Meaning the word *relief* may be in e.g. 20 cells in a range of 1000. So something like: vlookup "*relief", 'Master'!, O3:O1000,40,false -this lookup would be on another sheet. And if 'relief' was found in rows 26,39,67,75,102, 159, etc., could these results be put in rows 3 to 23, so I don't have to scroll down looking for them. Much thanks, Steve |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup of specific text, then in consecutive rows
Auto Filter Custom Filter select contains type in relief OK out
"Steve" wrote: I'd like to have vlookup return a name if a cell contains the word relief. And also in consecutive rows. Meaning the word *relief* may be in e.g. 20 cells in a range of 1000. So something like: vlookup "*relief", 'Master'!, O3:O1000,40,false -this lookup would be on another sheet. And if 'relief' was found in rows 26,39,67,75,102, 159, etc., could these results be put in rows 3 to 23, so I don't have to scroll down looking for them. Much thanks, Steve |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup of specific text, then in consecutive rows
Ok, thanks,
That will get me all the 'reliefs' in order, but many of the reliefs are duplicates, meaning relief 1 might be Smith, and Jones, and Mason. My V lookup is producing multi Smiths if Smith is the first it found matching relief 1. I need the names of each match. The B column has the filtered reliefs in it. Column 40 in the master tab has the names I need. Here' my vlookup =VLOOKUP(B4,MasterTab!$O$21:$BE$982,40,FALSE) Thanks, Steve "Teethless mama" wrote: Auto Filter Custom Filter select contains type in relief OK out "Steve" wrote: I'd like to have vlookup return a name if a cell contains the word relief. And also in consecutive rows. Meaning the word *relief* may be in e.g. 20 cells in a range of 1000. So something like: vlookup "*relief", 'Master'!, O3:O1000,40,false -this lookup would be on another sheet. And if 'relief' was found in rows 26,39,67,75,102, 159, etc., could these results be put in rows 3 to 23, so I don't have to scroll down looking for them. Much thanks, Steve |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup of specific text, then in consecutive rows
Presumably, B4 in the other sheet contains "relief."
Here's one approach with Excel 2003. In the other sheet put this in C2: =IF(ISNUMBER(FIND($B$4,MasterTab!O21)),MAX(C$1:C1) +1,"") and this in D2: =IF(ROW()-1MAX(C:C),"", OFFSET(MasterTab!$O$21,MATCH(ROW()-1,C:C,0)-2,39)) then select C2:D2 and copy down for the length of the list. Column D should contain the desired list. Column B is a helper column, and can be hidden. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup of specific text, then in consecutive rows
Ok, thanks.
I think we're close, but I can't get it to work, because I don't think I explained what I wanted properly. If I can begin again: In a blank, fresh sheet I want: to check the range on the "Master tab'! cells O21:O92, and if it finds the word relief, give me the result of the 40th column (BB) from whatever row it found the word relief. The cell it was found may be 'Master tab'!O36. So the result of 'master tab'! BB36 I would want in row 2 of my fresh sheet. The next find of relief might be in 'Master Tab'!O58. Give me the result of the 'Master tab'! BB58, and put that result in row 3 in my fresh sheet, etc. I hope this is a bit clearer. I could enter the word relief anywhere on the new sheet if that would make the lookup less wordy. And thanks in advance for you patience. Steve "zvkmpw" wrote: Presumably, B4 in the other sheet contains "relief." Here's one approach with Excel 2003. In the other sheet put this in C2: =IF(ISNUMBER(FIND($B$4,MasterTab!O21)),MAX(C$1:C1) +1,"") and this in D2: =IF(ROW()-1MAX(C:C),"", OFFSET(MasterTab!$O$21,MATCH(ROW()-1,C:C,0)-2,39)) then select C2:D2 and copy down for the length of the list. Column D should contain the desired list. Column B is a helper column, and can be hidden. . |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup of specific text, then in consecutive rows
The extra explanation clarifies things a bit, but the posted formulas
still work for me, if I understand the problem correctly. Could there be typos? Below is a slightly simplified version starting from a fresh sheet. In the fresh sheet put this in A2: =IF(ISERROR(FIND("relief",MasterTab!O21)),"",MAX(A $1:A1)+1) and this in B2: =IF(ROW()-1MAX(A:A),"", OFFSET(MasterTab!$BB$21,MATCH(ROW()-1,A:A,0)-2,0)) then select A2:B2 and copy down to row 73. Column B should contain the desired list. Column A is a helper column, and can be hidden. With your latest data, I get 1 in A17 and 2 in A39, corresponding to the rows in MasterTab!O21:O92 containing the word "relief" somewhere in the text. The rest of column A is blank. Then, B2 reports what's in MasterTab!BB36 and B3 reports what's in MasterTab!BB58. The rest of column B is blank. If it's still not working, there's a way to see step-by-step how Excel is evaluating a formula. Select the cell containing a formula and use: Tools Formula Auditing Evaluate formula This might give a clue how to correct a formula. (I have Excel 2003.) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup of specific text, then in consecutive rows
Very nice. 95% there.
I'm only getting #'s up to 9 in the A column, and also the corresponding 9 names in B2:B10, so that part is working great. However, I should have 22 incidents of relief instead of only 9. The reliefs might be in any row from Mastertab!O21:O923 Also, I'm dragging A and B on the fresh sheet to row 904, because that's where I'm getting the max ref to the row in the master tab where relief might be. The formula in cell A904 refs mastertab!O923. Also, the $'s in the B column formula are showing $BB$21 in cell B904. Could $BB$21 in cell B904 be causing the problem, meaning should it have been dragged without the $ ? Yes, Excel 2003 Thanks again, Steve "zvkmpw" wrote: The extra explanation clarifies things a bit, but the posted formulas still work for me, if I understand the problem correctly. Could there be typos? Below is a slightly simplified version starting from a fresh sheet. In the fresh sheet put this in A2: =IF(ISERROR(FIND("relief",MasterTab!O21)),"",MAX(A $1:A1)+1) and this in B2: =IF(ROW()-1MAX(A:A),"", OFFSET(MasterTab!$BB$21,MATCH(ROW()-1,A:A,0)-2,0)) then select A2:B2 and copy down to row 73. Column B should contain the desired list. Column A is a helper column, and can be hidden. With your latest data, I get 1 in A17 and 2 in A39, corresponding to the rows in MasterTab!O21:O92 containing the word "relief" somewhere in the text. The rest of column A is blank. Then, B2 reports what's in MasterTab!BB36 and B3 reports what's in MasterTab!BB58. The rest of column B is blank. If it's still not working, there's a way to see step-by-step how Excel is evaluating a formula. Select the cell containing a formula and use: Tools Formula Auditing Evaluate formula This might give a clue how to correct a formula. (I have Excel 2003.) . |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup of specific text, then in consecutive rows
I solved it .
It was only finding the lower case "r"elief, and I had some upper case "R"eliefs in the data also. By changing all the lower case r's to upper case R's, it worked perfectly. Not important, but just curious for future reference - Is there a way to somehow wildcard that formula to also find any cased relief ? Thanks again for all your patience. Steve "Steve" wrote: Very nice. 95% there. I'm only getting #'s up to 9 in the A column, and also the corresponding 9 names in B2:B10, so that part is working great. However, I should have 22 incidents of relief instead of only 9. The reliefs might be in any row from Mastertab!O21:O923 Also, I'm dragging A and B on the fresh sheet to row 904, because that's where I'm getting the max ref to the row in the master tab where relief might be. The formula in cell A904 refs mastertab!O923. Also, the $'s in the B column formula are showing $BB$21 in cell B904. Could $BB$21 in cell B904 be causing the problem, meaning should it have been dragged without the $ ? Yes, Excel 2003 Thanks again, Steve "zvkmpw" wrote: The extra explanation clarifies things a bit, but the posted formulas still work for me, if I understand the problem correctly. Could there be typos? Below is a slightly simplified version starting from a fresh sheet. In the fresh sheet put this in A2: =IF(ISERROR(FIND("relief",MasterTab!O21)),"",MAX(A $1:A1)+1) and this in B2: =IF(ROW()-1MAX(A:A),"", OFFSET(MasterTab!$BB$21,MATCH(ROW()-1,A:A,0)-2,0)) then select A2:B2 and copy down to row 73. Column B should contain the desired list. Column A is a helper column, and can be hidden. With your latest data, I get 1 in A17 and 2 in A39, corresponding to the rows in MasterTab!O21:O92 containing the word "relief" somewhere in the text. The rest of column A is blank. Then, B2 reports what's in MasterTab!BB36 and B3 reports what's in MasterTab!BB58. The rest of column B is blank. If it's still not working, there's a way to see step-by-step how Excel is evaluating a formula. Select the cell containing a formula and use: Tools Formula Auditing Evaluate formula This might give a clue how to correct a formula. (I have Excel 2003.) . |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup of specific text, then in consecutive rows
Is there a way to somehow wildcard that formula
to also find any cased relief ? Yes, easily. First, look at the formula for column A. It can be paraphrased this way: If (a) the row is to be excluded Then (b) return "" Otherwise (c) return the next higher unused number. In this example, (a) is the ISERROR(...) test. The (a) part of the solution is the *only* thing that filters the original data so that just the desired rows show up in the final list. For different examples, (a) can be any test of any values in the row. One can change only (a), leaving everything else the same, and achieve all sorts of filtering possibilities. For example, to make the test case-insensitive (a) might be: ISERROR(FIND("relief",LOWER(MasterTab!O21))) For another example, to disallow duplicate names, (a) might be: OR(ISERROR(...), COUNTIF(MasterTab!BB$21:BB21,MasterTab!BB21)1) Modify to suit. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup of specific text, then in consecutive rows
For another example, to disallow duplicate names, (a) might be:
* *... Caution: this example has an error. It should check for duplicates among the "relief" rows only, but it mistakenly looks at all the rows. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete rows with specific text | New Users to Excel | |||
Delete Rows Without Specific Text | Excel Worksheet Functions | |||
Transposing data from consecutive rows into non-consecutive rows | Excel Discussion (Misc queries) | |||
Copy/Paste Rows that Contain Specific Text | Excel Worksheet Functions | |||
Function to Count Number of Consecutive Rows with a Specific Criteria? | Excel Worksheet Functions |