Method to Concatenate Columns if there are duplicate entries in Rows

**navs8603** · 02-13-2012, 06:33 AM

Dear All,

My source table looks like below:

Title	Area	Category
Finance Risk	Finance	Budget
Finance Risk	Finance	Spend
Finance Risk	Finance	Cost
IT System	IT	Process
IT System	IT	Governance

But since there are multiple 'categories' attached with single Risk, I have to convert them like this for all duplicate values except the 3rd column as below:

Title	Risk	Category
Finance Risk	Finance	Budget / Spend / Cost
IT System	IT	Process / Governance

Any help would be tremendously appreciated.. I am struggling to get an idea on this....

Thanks,
Navaneeth

**JBeaucaire** · 02-13-2012, 07:02 AM

This presumes your data is sorted the way you demonstrated... if it is not, then sort the data by column A, then column B.

1) Put this formula in D2:

=A2&"-"&B2

2) In E2:

=IF(AND(A2=A3, B2=B3), D3 & " / " & C2, C2)

3) In F2:

=IF(COUNTIF($D$2:$D2, $D2) = 1, E2, "")

4) Now copy those three cells downward.

5) Highlight column F, and copy it

6) Click on column C and do an Edit > Paste Special > Values to paste the new values over the old

7) Clear columns D:F

8) Highlight column C and turn on the Data > Filter (AutoFilter)

9) Use the drop down to filter the column for Blanks

10) Highlight all the visible rows and delete them

11) Turn off the Data > Filter (AutoFilter)

**navs8603** · 02-13-2012, 07:29 AM

Hi JBeaucaire,

I tried your comment, but it gives error during this step =IF(COUNTIF($D$2:$D2, $D2) = 1, E2, "") - #Value! error. Any idea why that might be?

Thanks
Navaneeth.