Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
    
Have you tried anything? Parsing Part#1 to break up entries with semicolons looks like a promising way to start. –  pnuts Oct 16 '12 at 8:13
    
@jonsca Thank you for the massive layout improvement. –  pnuts Oct 16 '12 at 9:41
2  
@pnuts 99% of the credit goes to @slbarts, he proposed it. –  jonsca Oct 16 '12 at 13:36

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.