Excel Formula for combining data

[jlr123]

I have raw data which has the same address for two or more customer numbers. I need to show one customer number in one column and the the other customer number(s) in an adjacent column with a summary of Amt by Department. I have tried to utlize pivot table but unable to get the information the way we need to see it. Is there a formula that I can use either in raw data or pivot table to get this visualized as needed? Or perhaps there is another way to do this? Please find sample of raw data. Need response as soon as possible. Thanks very much.

 

[jlr123]

I have added a new raw data sheet for this thread.

 

[Andrzejek]

I need to show one customer number in one column and the the other customer number(s) in an adjacent column with a summary of Amt by Department.

Could you show here how that would look like? Maybe another Excel attachment here?

Is the VBA solution acceptable?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[jlr123]

Please see attached.

 

[SkipVought]

What if there are more than 2 Cust No for any ShipToAddress1, ShipTo City, ShipToState, ShipToZipCode?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

Also, in your raw data:[pre]
Dpt Cust No
CAL 15000.50
EQS 0121.169
ESL
REN[/pre]

but then in your 'Revised' version:[pre]
Dpt Cust No
CAL [red] 0121.51[/red]
EQS [red] 15000.52[/red]
ESL[red]
ESO
FLS
GMFG
MFG
ONS[/red]
REN[red]
TOOL
TRNG[/red][/pre]

Where do [red]these[/red] come from

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[jlr123]

The second one should be used for solution. I am waiting on Management to get back to me regarding ,more than one customer number starting with "0" and more than one 15000 account number. Will get back to you. Thanks!

 

[SkipVought]

It's more than 2 Cust Nos.

Having the Cust Nos in this report is irrelevant. ShipTo... is what's important.

Here's your report modified with only ShipToAddress and your original source data converted to a Structured Table, showing the ShipToAddress, Dept Amt formula...

You can see the formula in D7, simply copied across.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!