Merging values of duplicated customers

[stefgrab]

Hi,
I have the following problem, I haven't yet figured out the possible solution.
I have a report from SAP with a lot of customers, in columns there are credit control area, company code, customer number, name etc. and then financial values like Outstanding, Credit Limit etc. The issue is that some of the customers are duplicated in the report as a result of not accurate Master Data. My task is to code a solution that will make a new row with the same information from A to G as in the first time of appearance of the given customer and then to sum the values for each remaining column. Then repeat until there won't be any duplicate. I am thinking about a loop of some kind, first filtering out the duplicate creating the new row and then going to the next but I am not sure if it will prove accurate.

Thanks for any help,

It's structured like that from A to S columns, example of filtered out duplicate:
[TABLE="width: 1824"]
<tbody>[TR]
[TD]CCC:Credit Cntl Area
[/TD]
[TD]ORG: Company code
[/TD]
[TD]Customer (Main)
[/TD]
[TD]Customer Name
[/TD]
[TD]Region
[/TD]
[TD]Customer (Cred.Mgmt)
[/TD]
[TD]Rep. group
[/TD]
[TD]Due
[/TD]
[TD]Outstanding
[/TD]
[TD]Credit Limit
[/TD]
[TD]Not Due
[/TD]
[TD]Overdue 1-30 days
[/TD]
[TD]Overdue 31-60 days
[/TD]
[TD]Overdue 61-90 days
[/TD]
[TD]Overdue 91-120 days
[/TD]
[TD]Overdue > 120 days
[/TD]
[TD]Bad Debt 91 - 120 (>0 x 50%)
[/TD]
[TD]Bad Debt > 120 (>0 x 100%)
[/TD]
[TD]Bad Debt
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1000000
[/TD]
[TD]Same name
[/TD]
[TD]1
[/TD]
[TD]AAAAAAAAAAAAA
[/TD]
[TD]XXXXXXXXXXXXXXX
[/TD]
[TD]$ 0.00
[/TD]
[TD]$ 0.00
[/TD]
[TD]$ 100.00
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]$ 0.00
[/TD]
[TD][/TD]
[TD]$ 100
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]1000000
[/TD]
[TD]Same name
[/TD]
[TD]1
[/TD]
[TD]BBBBBBBBBB
[/TD]
[TD]YYYYYYY
[/TD]
[TD]$ 0.00
[/TD]
[TD]$ 0.00
[/TD]
[TD]0.00
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]$ 100.00
[/TD]
[TD][/TD]
[TD]$ 0
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Rijnsent]

Hi Stefgrab,

With just some formulae you should get quite far, generally I'd like to use that power of Excel:
Column T, cell T2: =COUNTIF($B$1:$B2;B2) -> this should give you the number of times a Company Code is in your list, anything with a 1 is the first time something occurs, the others are second, third etc time, the magic is in the $ sign missing.
Column U, cell U2: =SUMIF($B$2:$B$23;$B2;H$2:H$23) -> this should add up the values of column H for that company code, drag that formula to the right to do the same for the other columns.

After adding those columns, filter your whole range on Column T = 1, hide the columns H-T and you've got your data, copy-paste values to a new worksheet to get your "clean data". Of course you could record that macro code to automate the process. If you get stuck with that, don't hesitate to add that question to this thread.

Cheers,

Koen