How to combine data and delete row

  • Thread starter Thread starter Norman Goldsmith
  • Start date Start date
N

Norman Goldsmith

Can someone suggest a plan of attack for this problem?

Given this input data:

5 abc foobar1
7 abc foobar2
2 efg foobar3
5 efg foobar3
9 efg foobar3
7 xyz foobar3

I'd like to create output like this:

5 abc foobar1
7 abc foobar2
2,5,9 efg foobar3
7 xyz foobar3

The original input data was a list sorted alphabetically on the second
column. All of the data are in text format - no numbers. Excel 2000.

The ouput follows a rule like: if the text in rowN, column2 matches the
text in rowN+1,column2 AND the text in rowN, col3 matches the text in
rowN+1, col3, then add the text in row N+1, col1 to the text in rowN,
col1,
separated by a comma and delete row N+1.

I think I'll have to create an additional 'output' column to hold the
comma separated values. I know there are lots of examples of
how to delete duuplicate rows available. I need help with the logic
statement that will go through the data range.

Thanks for any help,
Norm Goldmsith
 
I think you can create a two-dimensional array to hold all the values in
column1. Then use three nested for-next loops to iterate through your cells,
storing all the column1 values in the array when col2=col2 and col3=col3.
Column1 would be the inside-most loop.

Lee McCoy
 
Lee,
Thanks for the suggestion. I ended up with this solution:

Sub combine_and_delete()
Selection.CurrentRegion.Select 'position cursor anywhere in the data
TotalRows = Selection.Rows.Count 'counts the number of rows in the data
ActiveCell.Select 'upper left corner
Selection.End(xlDown).Select 'bottom of first column
For irow = TotalRows To 1 Step -1
If ActiveCell.Offset(0, 1) = ActiveCell.Offset(-1, 1) Then
If ActiveCell.Offset(0, 2) = ActiveCell.Offset(-1, 2) Then
ActiveCell.Offset(-1, 0).Value = ActiveCell.Offset(-1, 0) & "," &
ActiveCell.Offset(0, 0)
ActiveCell.EntireRow.Delete
End If
End If
ActiveCell.Offset(-1, 0).Select
Next irow
End Sub
 
Back
Top