Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have 2 sets of data. One set to be filled up by fetching relevant data from a data array.

DATA to be FILLED:

Part#1   Part#2

4021006  
3808587  
3870480  
3083410  
3873905  
3890030  
4002065  
3699803  
3930218  

ARRAY OF DATA:

Part#1                      Part#2

4021006;3808587                1  
3808587                        2  
3870480;3083410;4002065        3  
3083410                       34  
3873905                       54  
3890030                       32  
4002065;3930218               65  
3699803                       75  
3930218                       68  

I need to match Part#1 and find Part#2.

EXPECTED OUTPUT

Part#1  Part#2

4021006 1  
3808587 1;2  
3870480 3  
3083410 3;4  
3873905 54  
3890030 32  
4002065 3;65  
3699803 75  
3930218 65;68  

Can anyone help?

share|improve this question
2  
@pnuts 99% of the credit goes to @slbarts, he proposed it. – jonsca Oct 16 '12 at 13:36

Your question seems too localised to me to be worth explaining all the steps in detail in case of benefit to others, so in outline only:

  1. Parse Part#1 into individual 7 character strings (3 separate columns).
  2. Create a two column pivot table from these 3 columns and Part#2.
  3. Copy that pivot table, Paste Special, Values somewhere available (say G30), delete rows that display blank in any cell, sort in Part#1 order.
  4. Enter
    =IF(G29=G30=G31,H29&";"&H30&";"&H31,IF(G30=G31,H30&";"&H31,IF(G31<>G32,H31,"")))
    in I31 and copy down.
  5. Delete rows that display blank and use result as array to supply look up values to DATA to be FILLED.

Result is not EXPECTED OUTPUT because of inconsistency in the question.

Process is (a) make ARRAY OF DATA more manageable (by parsing). (b) 'combine' columns (with PivotTable). (c) String together Part#2 components (with formula). (d) Preserve seemingly arbitrary order (by LOOKUP).

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .