Copy pair of columns

[DConstance]

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 withou
the duplicates. I have many duplicates in B and C due to having the sam
part numbers/descriptions on various purchase orders. I'm going t
continue adding data to these 3 columns. I haven't successfully create
a formula to copy the contents of columns B and C without th
duplicates. Any ideas

 

[Claus Busch]

Hi,

Am Mon, 8 Jul 2013 11:41:13 +0100 schrieb DConstance:

In column A I enter purchase order numbers, in B part numbers and
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. Any ideas?

try advanced filter without duplicates.


Regards
Claus B.

 

[Volker Neurath]

In column A I enter purchase order numbers, in B part numbers and
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. Any ideas?

I think there is no Chance to do this by formula only.
After copying by formula you will have to remove duplicates by the
menufunction in menu's "Data" section
I suppose, dringend this by VBA would bei mich easyer
--

 

[zvkmpw]

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.