Home |
Search |
Today's Posts |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Need a fresh pair of eyes | Excel Worksheet Functions | |||
How to look at the values in 8 columns, pick the two that havenumbers, then compare to another pair of numbers | Excel Programming | |||
compare two text columns and find the closest matched pair of cells | Excel Programming | |||
Matching one pair of cells with another | Excel Programming | |||
Need code to pair off numbers | Excel Programming |