|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 13th, 2010, 11:40 AM
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  10000 ... 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 200 ... Need Formula to return each Op # (Ascend) found against PN above from WS1 ... Range D3 10000Again ... 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
April 13th, 2010, 12:40 PM
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 10000 ... 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 200 ... Need Formula to return each Op # (Ascend) found against PN above from WS1 ... Range D3 10000Again ... 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
April 19th, 2010, 06:31 PM
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 10000 ... 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 200 ... Need Formula to return each Op # (Ascend) foundagainst PN above from WS1 ... Range D3 10000Again ... 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 .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
