Excell: consolidate dupes and add missing dat Closed

Question

Hello,
I can's seem to find an answer anywhere for my problem.  I have a CSV list in excel that has companies contact information and what category they belong too.  Most of these companies belong in several categories.  Right now the list has a separate row for each category a business is in.  I need to consolidate this information into one row with the additional categories added to the information.  It will take weeks to do this by hand, i was wondering if someone more familiar with excel could help me out.


Example List:

COMPANY NAME,ADDRESS 1,ADDRESS 2,CITY,STATE,ZIP,PHONE,FAX,COUNTRY,COUNTRY ABBR,CATEGORY 1
Company 1,111 Main St,Suite 3,New York,NY,10001,555-555-5555,555-555-4444,United States,USA,CatagoryA->Sub Category7
Company 1,111 Main St,Suite 3,New York,NY,10001,555-555-5555,555-555-4444,United States,USA,CatagoryB->Sub Category8
Company 1,111 Main St,Suite 3,New York,NY,10001,555-555-5555,555-555-4444,United States,USA,CatagoryC->Sub Category9
Company 2,111 East St,Suite A,Washington,DC,20003,555-666-5555,555-666-4444,United States,USA,CatagoryA->Sub Category10
Company 2,112 East St,Suite A,Washington,DC,20004,555-666-5555,555-666-4444,United States,USA,CatagoryB->Sub Category11
Company 3,111 Main St,Suite c,Denver,CO,80202,888-555-5555,888-555-4444,United States,USA,CatagoryA->Sub Category16

Need list to look like this:

Company 1,111 Main St,Suite 3,New York,NY,10001,555-555-5555,555-555-4444,United States,USA,CatagoryA->Sub Category7,CatagoryB->Sub Category8,CatagoryC->Sub Category9,
Company 2,111 East St,Suite A,Washington,DC,20003,555-666-5555,555-666-4444,United States,USA,CatagoryA->Sub Category10,CatagoryB->Sub Category11,,
Company 3,111 Main St,Suite c,Denver,CO,80202,888-555-5555,888-555-4444,United States,USA,CatagoryA->Sub Category16,,,
Thanks in advance for the help

venkat1926 · Answer

the main data(example list) is in excel sheet1 in different columns using textocolumn with comma as delimiter.
now use this macro "test" and check
 whether you get what you want.
result is in sheet3 repeat sheet 3


Sub test()
Dim r As Range, rfull As Range, filt As Range, coname As Range, dest As Range, cname As Range
Dim j As Long, k As Long, colk() As Range, commonrow As Range
With Worksheets("sheet1")
Set r = Range(.Range("A1"), .Range("A1").End(xlDown))
Set rfull = .Range("A1").CurrentRegion
Set coname = .Range("A1").End(xlDown).Offset(5, 0)

r.AdvancedFilter xlFilterCopy, , coname, True

For Each cname In Range(coname.Offset(1, 0), coname.End(xlDown))
rfull.AutoFilter field:=1, Criteria1:=cname.Value
Set filt = rfull.Offset(1, 0).Resize(Rows.Count - 1, Columns.Count).SpecialCells(xlCellTypeVisible)
j = filt.Rows.Count
'MsgBox j

If j > 1000 Then j = 1
ReDim colk(1 To j)
 Range(filt.Cells(1, 1), filt.Cells(1, "J")).Copy Worksheets("sheet3").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
 For k = 2 To j
filt.Cells(k, "K").Copy Worksheets("sheet3").Cells(Rows.Count, "K").End(xlUp).Offset(1, 0)
Next k
rfull.AutoFilter
j = 0
Next cname
Range(.Range("A1").End(xlDown).Offset(1, 0), .Cells(Rows.Count, "A")).EntireRow.Delete
End With
With Worksheets("sheet3")
Range(.Range("A1"), .Range("A1").End(xlToRight)).EntireColumn.AutoFit
End With
End Sub


Sub undo()
Worksheets("sheet3").Cells.Clear
End Sub

Tj · Answer

This removes the duplicates, but does not add the categories properly.  The categories are still listed in one column.   The categories for a listing should all be in the same row, behind the main information.  I appreciate the help!

venkat1926 · Answer

what I did was copy csv file into one column of excel slhseet and convert csv data  into an excel using comma as delimiter. I hope you have done that .
then only the macro can be run
the results will be in sheet3 which is an excel sheet .

I wonder is there a way to transfer files in this forum. altesrnatively you can get permission of the administrator of this forum and send your file to my email address explaining your problem

Tj · Answer

so, I'm guessing this can't be done with a macro?  Guess I'll have to do it by hand.

venkat1926 · Answer

TJ

don't be despondent.  practically for anything in excel macro can be written. let me look at your file

venkat1926 · Answer

i have truncated your data sheet and called the file "TJ.xls".  In future it is not necessary to send the complete data sheet. a small extract (few rows) will be enough.
i have uploaded the file

the result is in sheet3. You see sheet 3 AFTER RUNNING THE MACRO TEST.
in this sheet see the data particularity in column K.

for retesting run undo and again run test on this truncated file
if ok you can copy the macro in your original file. Before running the macro save the original file safely somewhere.

but there may be problem for e.g. for Hampden Engineering Corp
there are 5 rows and the values from col A to J are same . But col K are different for these 5 rows. these data in these 5 rows in column K are copied in column K of sheet 3 successively with a comma in between. this long data is copied in K2. but remember in excel 2003 and earliler version the length of entries in a cell should be less than 256 characters. If the length of all the K column K of data sheet(master llist2) there will be problem.

the file is uploaded in the web page address
http://hotfile.com/dl/134002646/f4e68c7/TJ.xls.html

your comments please

Excell: consolidate dupes and add missing dat

6 responses

Subscribe To Our Newsletter!