Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching down a column
Hi all and thanks ahead of time for all help and suggestions provided.
Typically I would search for the answer but unfortunately time is not on my side. Here's part of my sheet1 set up. D3:Q3 I have headers for my columns. A2 has the name of a district that can be selected. (I used data.validation to create a drop down) Here's the results I'm after. In cells B4:B15 I need to get the store numbers that match the district in A2 by referencing sheet2. (sheet2 has a column with the districts and their matching stores in the next column) I tried to use "Index,Match" but the results are only the first store that matches the district. Again Thanks for any suggestions provided. also if I've missed some detail, sorry about that. Again I'm in a bit of a rush, not at all by choice. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching down a column
Here's one way to set it up to return the multiple matches that you're after
... In Sheet2, where you have your source data, assume that the districts are running in B2 down, with the store#s in C2 down In Sheet1, your DV for selection of the district is in A2 Put in B4: =IF(ROWS($1:1)COUNT(C:C),"",INDEX(Sheet2!C:C,SMAL L(C:C,ROWS($1:1))-2)) Put in C4: =IF(A$2="","",IF(Sheet2!B2=A$2,ROW(),"")) Leave C1:C3 blank Select B4:C4, copy down to cover the max expected extent of source data in Sheet2, say down to row100. Hide away col C. Col B will return the required results, all neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tsunami3169" wrote in message oups.com... Hi all and thanks ahead of time for all help and suggestions provided. Typically I would search for the answer but unfortunately time is not on my side. Here's part of my sheet1 set up. D3:Q3 I have headers for my columns. A2 has the name of a district that can be selected. (I used data.validation to create a drop down) Here's the results I'm after. In cells B4:B15 I need to get the store numbers that match the district in A2 by referencing sheet2. (sheet2 has a column with the districts and their matching stores in the next column) I tried to use "Index,Match" but the results are only the first store that matches the district. Again Thanks for any suggestions provided. also if I've missed some detail, sorry about that. Again I'm in a bit of a rush, not at all by choice. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching down a column
On Jul 31, 6:28 pm, "Max" wrote:
Here's one way to set it up to return the multiple matches that you're after .. In Sheet2, where you have your source data, assume that the districts are running in B2 down, with the store#s in C2 down In Sheet1, your DV for selection of the district is in A2 Put in B4: =IF(ROWS($1:1)COUNT(C:C),"",INDEX(Sheet2!C:C,SMAL L(C:C,ROWS($1:1))-2)) Put in C4: =IF(A$2="","",IF(Sheet2!B2=A$2,ROW(),"")) Leave C1:C3 blank Select B4:C4, copy down to cover the max expected extent of source data in Sheet2, say down to row100. Hide away col C. Col B will return the required results, all neatly bunched at the top. -- Max Singaporehttp://savefile.com/projects/236895 xdemechanik ---"Tsunami3169" wrote in message oups.com... Hi all and thanks ahead of time for all help and suggestions provided. Typically I would search for the answer but unfortunately time is not on my side. Here's part of my sheet1 set up. D3:Q3 I have headers for my columns. A2 has the name of a district that can be selected. (I used data.validation to create a drop down) Here's the results I'm after. In cells B4:B15 I need to get the store numbers that match the district in A2 by referencing sheet2. (sheet2 has a column with the districts and their matching stores in the next column) I tried to use "Index,Match" but the results are only the first store that matches the district. Again Thanks for any suggestions provided. also if I've missed some detail, sorry about that. Again I'm in a bit of a rush, not at all by choice.- Hide quoted text - - Show quoted text - Thanks tons! I had to tweak it a little, I'm not sure why. Once I removed the -2 it worked like a charm. Thanks again |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching down a column
The suggested set-up was to closely suit what you described in your post.
The "-2" is a required arithmetic adjustment as the criteria col C's formula starts in C4 down (as you said the results were to be in B4 down) while the source data in Sheet2 was assumed to start in row 2 down (this was assumed as it wasn't mentioned in your post). Anyway I'm glad you tweaked it to suit what you really had over there <g. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tsunami3169" wrote in message oups.com... Thanks tons! I had to tweak it a little, I'm not sure why. Once I removed the -2 it worked like a charm. Thanks again. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
column matching | Excel Discussion (Misc queries) | |||
matching from one column to another | Excel Discussion (Misc queries) | |||
matching from one column to another | Excel Discussion (Misc queries) | |||
matching data from column b | Excel Worksheet Functions | |||
matching the column b(sku)c(count)with A | Excel Worksheet Functions |