Concatenate duplicates + delete + more steps

[davidhutchison1]

I have 2 columns of data with 7,000 rows that I am trying to manipulate in the following way. The first column contains a list of car manufacturers (not really my example, but involves the same concept). The second column contains a list of different models of cars. For instance,

Ford | Taurus
Chevrolet | Avalanche
Saturn | Sky
Ford | Explorer
Honda | Accord
Ford | Taurus

What I want is a list of 2 columns with UNIQUE car manufacturers in first column with a list of UNIQUE car models (comma delimited) in second column. So for this example, the first row could read " Ford | Taurus,Explorer". I don't want Taurus to appear twice. I got a lot of help from JBeaucaire from this post. I just can't add the ability to remove car models from the list also. Thanks in advance for all the help!

[pike]

david
code delete ..... I try again forgot about the dups

[pike]

hi david
for 7,000 rows try using a pivot table

[pike]

Here you go

Please Login or Register  to view this content.

when you dont use something for a while you forget the tricks

[davidhutchison1]

Pike, this works perfectly! Can you modify the code to remove any spaces in the resulting concatenated column though? I'm talking about the preceding spaces before the first entry in each resulting cell (I could use the trim function in excel but having it in VBA would be a lot easier) as well as the spaces after each comma if there are multiple car models for each car manufacturer.

Thanks a lot for the help!

[pike]

daveo
try changing the IIF code to

Please Login or Register  to view this content.

The IIF script adds the spacing to and between the items

[davidhutchison1]

Actually it was the space in red below that was causing all the spacing:

Please Login or Register  to view this content.

Once I changed it to "" it removed the spaces in the beginning on the cell and the spaces after the commas. Thanks anyways!

[pike]

My bad
well done

try..

Please Login or Register  to view this content.

please dont forget to change the post to solved and rate help

[trent_mac]

Hi Pike

I have a simliar issue and have been trying to solve it my self for 3 days but cant get anywhere with it, the id number can be different but same value 331, 0331 but all the names are the exact same.

ID	name	website	email	Phone	package	cat_i
650	Next inc	www."".com	aa@"".com	111	TLI10	201
650	Next inc	www."".com	aa@"".com	111	AER	151
31	NEXUS	www."".com	aa@"".com	222	AER	151
031	NEXUS	www."".com	aa@"".com	222	AEC	425
031	JUST CORP	www."".com	aa@"".com	333	TLI10	201
15496	ACME inc	www."".com	aa@"".com	444	TLI10	201
15496	ACME inc	www."".com	aa@"".com	444	RMS	245
15496	ACME inc	www."".com	aa@"".com	444	AEC	425

i need to run a macro to change it to

ID	name	website	email	Phone	package	cat_i
650	Next inc	www."".com	aa@"".com	111	TLI10, AER	201,151
31	NEXUS	www."".com	aa@"".com	222	AER, AEC	151, 425
031	JUST CORP	www."".com	aa@"".com	333	TLI10	201
15496	ACME inc	www."".com	aa@"".com	444	TLI10, RMS, AEC	201, 245, 425

Please help if you can i have 18000 rows and i have been manualy doing data entry and have only got 2000 rows done this will take me weeks to do

[FDibbins]

trent, forum rules state that you cannot piggy=back onto an existing thread. Ypu need to start a new thread with your request