| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Offset/Index/Match ... or something?
Excel2003 ...
WS1 ... contains list of all PN/Ops & Cols of other data ... WS1 ... Range A2:A10000 ... contains record count (1 thru 9,999) WS1 ... Range B2:B10000 ... contains Dept #'s WS1 ... Range C2:C10000 ... contains PN's (may repeat up to 110 times) WS1 ... Range D2:D10000 ... contains Op #'s (sort = PN/Op# Ascend) WS2 ... has identical Col layout & format, with intent to be for 1 PN only (WS1 is list of all PNs) ... I could do this by Filtering PN on WS1 & then Copy/Paste to WS2, However, I wish to: WS2 ... Cell C2 ... enter a PN WS2 ... Range C3:C200 ... Need Formula to return PN found in WS2 Cell C2 as many times as found on WS1 Range C2:C10000 ... otherwise, return a Blank (so if PN appeared on WS1 65 times ... the 1st 65 cells of WS2 Range C2:C200 would contain the PN with remaining cells in Range containing a Blank) WS2 ... Range B2:B200 ... Need Formula to return Dept # found against PN above from WS1 ... Range B2:B10000 WS2 ... Range D3:D200 ... Need Formula to return each Op # (Ascend) found against PN above from WS1 ... Range D3:D10000 Again ... WS1 contains ALL PN/Ops (sort = Ascend) WS2 to contain same data for 1 PN only based on PN entered in Cell C2 My "Thanks" in advance to those of you who are intimate with Excel & provide the many valuable solutions found on these boards ... Kha |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Offset/Index/Match ... or something?
Hi Ken,
See: http://lounge.windowssecrets.com/ind...owtopic=771787 -- Cheers macropod [Microsoft MVP - Word] "Ken" wrote in message ... Excel2003 ... WS1 ... contains list of all PN/Ops & Cols of other data ... WS1 ... Range A2:A10000 ... contains record count (1 thru 9,999) WS1 ... Range B2:B10000 ... contains Dept #'s WS1 ... Range C2:C10000 ... contains PN's (may repeat up to 110 times) WS1 ... Range D2:D10000 ... contains Op #'s (sort = PN/Op# Ascend) WS2 ... has identical Col layout & format, with intent to be for 1 PN only (WS1 is list of all PNs) ... I could do this by Filtering PN on WS1 & then Copy/Paste to WS2, However, I wish to: WS2 ... Cell C2 ... enter a PN WS2 ... Range C3:C200 ... Need Formula to return PN found in WS2 Cell C2 as many times as found on WS1 Range C2:C10000 ... otherwise, return a Blank (so if PN appeared on WS1 65 times ... the 1st 65 cells of WS2 Range C2:C200 would contain the PN with remaining cells in Range containing a Blank) WS2 ... Range B2:B200 ... Need Formula to return Dept # found against PN above from WS1 ... Range B2:B10000 WS2 ... Range D3:D200 ... Need Formula to return each Op # (Ascend) found against PN above from WS1 ... Range D3:D10000 Again ... WS1 contains ALL PN/Ops (sort = Ascend) WS2 to contain same data for 1 PN only based on PN entered in Cell C2 My "Thanks" in advance to those of you who are intimate with Excel & provide the many valuable solutions found on these boards ... Kha |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Offset/Index/Match ... or something?
Don ... (Hi)
I managed to figure this out & now have it working as desired ... Thanks for supporting these boards ... Many fine solutions have been learned here ... Kha "Don Guillett" wrote: If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software "Ken" wrote in message ... Excel2003 ... WS1 ... contains list of all PN/Ops & Cols of other data ... WS1 ... Range A2:A10000 ... contains record count (1 thru 9,999) WS1 ... Range B2:B10000 ... contains Dept #'s WS1 ... Range C2:C10000 ... contains PN's (may repeat up to 110 times) WS1 ... Range D2:D10000 ... contains Op #'s (sort = PN/Op# Ascend) WS2 ... has identical Col layout & format, with intent to be for 1 PN only (WS1 is list of all PNs) ... I could do this by Filtering PN on WS1 & then Copy/Paste to WS2, However, I wish to: WS2 ... Cell C2 ... enter a PN WS2 ... Range C3:C200 ... Need Formula to return PN found in WS2 Cell C2 as many times as found on WS1 Range C2:C10000 ... otherwise, return a Blank (so if PN appeared on WS1 65 times ... the 1st 65 cells of WS2 Range C2:C200 would contain the PN with remaining cells in Range containing a Blank) WS2 ... Range B2:B200 ... Need Formula to return Dept # found against PN above from WS1 ... Range B2:B10000 WS2 ... Range D3:D200 ... Need Formula to return each Op # (Ascend) found against PN above from WS1 ... Range D3:D10000 Again ... WS1 contains ALL PN/Ops (sort = Ascend) WS2 to contain same data for 1 PN only based on PN entered in Cell C2 My "Thanks" in advance to those of you who are intimate with Excel & provide the many valuable solutions found on these boards ... Kha . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| OFFSET vs INDEX(MATCH(...)) | Excel Discussion (Misc queries) | |||
| using OFFSET(INDEX(MATCH))) | Excel Discussion (Misc queries) | |||
| INDEX, MAX, OFFSET, MATCH | Excel Worksheet Functions | |||
| index match offset? | Excel Worksheet Functions | |||
| Index, Match, Offset? Not sure which to use | Excel Worksheet Functions | |||
Linear Mode
