Combining rows of data, only if unique

[futbolfan]

I have data that I have extracted from another program and would like to condense the rows of data in Exce; but not sure which formulas to use to do so. Below is an example of what the data originally looks like, and what I would like it to be condensed to once complete.

Can anyone offer some thoughts on the steps to take to condense the data?

ORIGINAL:
A B C D
1,000 Corporate Center UT LINKS (NW4)
1,000 Corporate Center GR LINKS (NW4)
1,000 Corporate Center PB LINKS (NW4)
1,000 Corporate Center BE LINKS (NW4)
1,000 Neighborhood UT LINKS (NW4)
1,000 Neighborhood DEN LINKS (NW4)
1,000 Neighborhood PB LINKS (NW4)

REVISED:
A B C D
1,000 Corporate Center, Neighborhood UT, GR, OB, BE, UT, DEN LINKS (NW4)


I need the revised row to compile only the unique values from the original dataset

Any advice or direction would be very helpful.

Thanks!

[k64]

You can use this formula:

Please Login or Register  to view this content.

. Then, copy and paste as values, use the "remove duplicates tool", and copy and paste transposed.

[futbolfan]

You can use this formula:

Please Login or Register  to view this content.

. Then, copy and paste as values, use the "remove duplicates tool", and copy and paste transposed.

Thanks for the reply.

I pasted the formula at the end of the row in Column G and copied it down and then removed the duplicates.
However I have about 250+ rows of data and when I paste and transpose its a long list across the columns.
As well I need the values from column D and column E grouped into one cell, if that is possible.

I have attached a sample workbook.

Rows 2 to 6 are associated with LINK 1. I want all of the information displayed in one row.

Ex,
LINK 1 ME9 Meadowtown CN, Huronia NHD, Rathwood NHD HD, G, MD, OS


Is it possible to collapse the rows of data that only the unique information remains?

Thanks!

[k64]

Is this what you want?

[futbolfan]

Is this what you want?

Not exactly.

I have attached another workbook and the columns to the right in row 2 and row 68 I have shown how I want the fields collapsed.

I should mention CENT is the main column. LINK1 shows up 5 times, but in the final version I want it all collapsed into one row and only the unique data to appear in the final output.

Thanks!

[k64]

Well, it took me a while to get this to work, but I think I finally have it.

[futbolfan]

Well, it took me a while to get this to work, but I think I finally have it.

Thank you for your help with this.

The one thing I caught is that there are duplicates in the DESIG field - but I can manually edit that if I have to.

I see that you built a macro, how easy is it to move from one spreadsheet to another and do I have to ensure that the document is always set-up in this manner?

[k64]

You're welcome
I'm not sure what you mean about duplicates in the DESIG field. For different CENT numbers, there are duplicates, but I thought that is what you wanted. For each CENT "group" I have all the AREAs and DESIGs without any duplicates.

It is really easy to move to another spreadsheet. You simply need to copy my macro, insert a new module on the new spreadsheet, and paste my macro in. Yes, you'll need to have the same set-up, including my helper column in A, or you'll need to edit my macro for a different setup.

[futbolfan]

If you look at the last entry:

LINK9 HO6 137332 Uptown MN, Huronia NHD G, HD, PBW, G, HD, LDII, PBW

G, PBW and HD are repeated, I need those to only appear once, similar to how the AREA appears only once, sorry if that wasnt clear in my initial post.

[k64]

That's interesting. It only happens for the last row. I'll have to check why my code is doing that and get back to you.

[k64]

Found the problem. This should be better.

[futbolfan]

Found the problem. This should be better.

Thank you, that was better.

I realized one of my datasets had a few different columns, but I tried to set up the document in the same way and the macro does everything except group the items on the same line, per the attached.
I tried to look through the macro code and figure it out, but that is above and beyond my capabilities.
Is it possible to adjust the macro for this dataset?

[k64]

It seems to work for me. I added some comments to my code. I'm basically looping through the cells in column B and if I find a new value, I record the row number, remove duplicates between this number and the last time column B changed, and then loop through whatever is left, adding each value into one cell.

[futbolfan]

Sorry for the delay in responding. When I sorted the first column from smallest to largest and then run the macro it worked for me, otherwise it was crashing. Thanks very much for the help!

[k64]

The first column should be the formula

Please Login or Register  to view this content.

in A2, dragged down.