View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.misc
zvkmpw zvkmpw is offline
external usenet poster
 
Posts: 153
Default Copy pair of columns

In column A I enter purchase order numbers, in B part numbers an
descriptions in C. I need to copy the data in columns B and C without
the duplicates. I have many duplicates in B and C due to having the same
part numbers/descriptions on various purchase orders. I'm going to
continue adding data to these 3 columns. I haven't successfully created
a formula to copy the contents of columns B and C without the
duplicates.


One way is to use helper columns. Below, E and F are helper columns. The results are columns G and H; namely, the unique pairs of part numbers and descriptions, with no gaps. I'm assuming the original data starts in row 2.

In E2, put
=B2& " ~ " & C2
This concatenates the part numbers and descriptions for detecting duplicates.

In F1 put the number 1 (one).

In F2 put
=IF(COUNTIF(E$1:E1,E2)=0,MAX(F$1:F1)+1,"")
This flags the first of each unique pair, and numbers them sequentially.

In G2 put
=IF(ROW()MAX(F:F),"",INDEX(B:B,MATCH(ROW(),F:F,0) ))
This gets the sequential flagged part number corresponding to this row.

In H2 put
=IF(ROW()MAX(F:F),"",INDEX(C:C,MATCH(ROW(),F:F,0) ))
This gets the sequential flagged description corresponding to this row.

Select E2:H2 and copy down past the end of the data.

Columns F and G can be hidden to eliminate clutter.

Modify as needed. Hope this helps getting started.