|
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 |
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 |
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 . |
| All times are GMT +1. The time now is 10:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com