Excel – Comma Separated Values in Excel 2010

Here's a non-VBA, non-pivot table solution that only uses a couple of formulas.

1. First, I used the "Text-to-columns" to split your data at that "pipe" delimiter (the vertical line) into 2 columns; a "Supplier" column and a "Product" column. Those go in columns A and B, respectively. (It appears in your post that they are combined in one column, so I first split them apart. You won't have to do this.)

2. In column C, which I named as the "Concatenation" column, I used this formula, starting in cell C2 and copying all the way down: =IF(A2=A1,C1&", " & B2,A2&"|"&B2)

3. In column D, which I named as "SupplierChangesAtNextLine?" I used this formula (starting in D2 and copying all the way down): =IF(A2=A3,"","Changed")

4. You should now be able to filter on column D for only the "changed" values.

Good hunting!

I found the problem. My decimal symbol in the Regional settings was also a comma (European) so, even when my List separator was a comma, the CSV was saved with semicolons. Now I changed my decimal symbol to a point and now the CSV file is created correctly with commas as separators. I tested this twice and now know that there must be an exception: if the decimal symbol is a comma, then the list separator will be a semicolon even is set otherwise.