Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Expanding on the INDEX function
Im using the following formula to and am wondering if it can be used more
efficiently, or if I need something completely different. =INDEX($A$12:$A$43,MATCH(Q12,$N$12:$N$43,0)) Data from column N is sorted from highest to lowest into column Q in my Excel spreadsheet. I have duplicates in column Q, so the above formula Im using isnt working like I need it to. I need the formula to continue to the next occurrence in column N when a duplicate is encountered and place that result from column A into column S. I need this to continue as such as need be. I have over ten duplicate numbers in column Q, but those duplicate numbers represent ten different items. For example I have this data: 1 DMWF 1 DMWF 1 DMWF 0 CC 0 CC 0 CC However, it should look like this: 1 DMWF 1 HD 1 P (LC) 0 CC 0 LTC 0 HTOAC Any help would be appreciated. Thank you. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Expanding on the INDEX function
I'm thinking you'd need to use the COUNTIF function in some way. Without
seeing more data, I can't help. Try putting this in an adjacent column to see what it gets you. =COUNTIF(Q12,Q$12:Q12) -- HTH, Barb Reinhardt "NM" wrote: Im using the following formula to and am wondering if it can be used more efficiently, or if I need something completely different. =INDEX($A$12:$A$43,MATCH(Q12,$N$12:$N$43,0)) Data from column N is sorted from highest to lowest into column Q in my Excel spreadsheet. I have duplicates in column Q, so the above formula Im using isnt working like I need it to. I need the formula to continue to the next occurrence in column N when a duplicate is encountered and place that result from column A into column S. I need this to continue as such as need be. I have over ten duplicate numbers in column Q, but those duplicate numbers represent ten different items. For example I have this data: 1 DMWF 1 DMWF 1 DMWF 0 CC 0 CC 0 CC However, it should look like this: 1 DMWF 1 HD 1 P (LC) 0 CC 0 LTC 0 HTOAC Any help would be appreciated. Thank you. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Expanding on the INDEX function
The results I am looking for are the matching text in column A based on what
data in column Q matches data in column N. - I have data in column N - I have that same data sorted in column Q using =LARGE($N$12:$N$43,1) - I want the results in column Q to find their match in column N then have the corresponding data in column A from that row to show in column S. The INDEX formula works fine, but if there are duplicate results in column Q its search in column N always stops at the first instance it matches. This results in multiple duplicates from column A, when in fact, no duplicate data from column A should appear. I think I explained it better, here. :-) "Barb Reinhardt" wrote: I'm thinking you'd need to use the COUNTIF function in some way. Without seeing more data, I can't help. Try putting this in an adjacent column to see what it gets you. =COUNTIF(Q12,Q$12:Q12) -- HTH, Barb Reinhardt "NM" wrote: Im using the following formula to and am wondering if it can be used more efficiently, or if I need something completely different. =INDEX($A$12:$A$43,MATCH(Q12,$N$12:$N$43,0)) Data from column N is sorted from highest to lowest into column Q in my Excel spreadsheet. I have duplicates in column Q, so the above formula Im using isnt working like I need it to. I need the formula to continue to the next occurrence in column N when a duplicate is encountered and place that result from column A into column S. I need this to continue as such as need be. I have over ten duplicate numbers in column Q, but those duplicate numbers represent ten different items. For example I have this data: 1 DMWF 1 DMWF 1 DMWF 0 CC 0 CC 0 CC However, it should look like this: 1 DMWF 1 HD 1 P (LC) 0 CC 0 LTC 0 HTOAC Any help would be appreciated. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index Function | Excel Worksheet Functions | |||
Index function | Excel Discussion (Misc queries) | |||
Index Function | Excel Discussion (Misc queries) | |||
Index Function/Match Function | Excel Discussion (Misc queries) | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |