0

I have an Excel or CSV file with 5,000 rows and 20 columns. I want to concatenate Column 6 and Column 8, look for duplicates and then copy only one of the duplicates into WorkSheet2.

For Example:

Worksheet1

Column6  Column7     Column 8

275s     ABC         123

333s     ABC         111

275s     ZZZ         123

275s     DSD         123

333s     ???         111

275s     asd         999

The result of concatenate Column 6 and column 8 will render that rows 1,3 and 4 are duplicates.

Row 2 and 5 are duplicates also.

Then worksheet2 should be:

Column6  Column7     Column 8

275s     ABC         123

333s     ABC         111

275s     asd         999
  • What do you mean by duplicate? Same value for Column6? Which value should be in Column7 abc, zzz or dsd. This can be done without VBA too. – Vojtěch Dohnal Jul 13 '15 at 15:20
  • Also, this isn't a script-writing service. Show us what you've done and we can help you figure out where your problems are. – variant Jul 13 '15 at 15:36
  • Column 7 is just there for filler .. only column 6 and 8 matter for comparison. – rob Jul 13 '15 at 15:44
  • dic.CompareMode = vbTextCompare 'NOT case sensitive. omit for case sensitive Set tsIn = fso.OpenTextFile("c:\temp\Input.csv") Set tsOut = fso.OpenTextFile("c:\temp\output.csv") Do Until tsIn.AtEndOfStream TheLine = tsIn.ReadLine If TheLine <> "" Then If dic.Exists(TheLine) Then Repeat = True Else Repeat = False dic.Add TheLine, TheLine End If Else Repeat = False End If If Not Repeat Then tsOut.WriteLine TheLine Loop tsIn.Close tsOut.Close – rob Jul 13 '15 at 15:44
  • Not sure how to enter code in a good format. – rob Jul 13 '15 at 15:45
0

VBA may not be necessary. Helper Columns are your friend here. Try the below formulas

  1. assuming Column6 is E and Column 8 is G and the below helper columns are starting in Column9 / H
  2. if you have your data in a DataTable, you have another syntax option

R1C1

  • =Concatenate( E2, F2, G2 ) <-- H
  • =Concatenate( E2, G2 ) <-- I
  • =CountIfs( I:I, I2 ) <-- J
  • =Row() <-- K
  • =IF( J2=1, H1, INDEX(H:H, AGGREGATE( 15,6,K:K/--(I:I=I2), 1 ) ) ) <-- L

Column L will return the first found row value in Column H for the unique concatenate value in Column I. If your dataset is very large, the Index( Aggregate() ) method may overflow and fail. But assuming it works, you can Copy-Paste Values Only from Column L into a new worksheet and Remove Duplicates ( Alt+A+M to bring up the dialogue ). If you delimit the concatenation in Column H, it will help to split out the values back into columns in the new worksheet.

  • A bit too lazy to post the DataTable syntax on the first pass ( sorry ). If you are using formatted DataTables and cannot figure out the conversion to stabilize the Index( Aggregate() ) method, please let me know and I will edit the answer accordingly. – Peter Vandivier Jul 14 '15 at 16:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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