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.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel Formula for combining data
- Thread starter jlr123
- Start date
- Thread starter
- #2
- Thread starter
- #4
SkipVought
Programmer
What if there are more than 2 Cust No for any ShipToAddress1, ShipTo City, ShipToState, ShipToZipCode?
Skip,
[sub]
![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
Just traded in my OLD subtlety...
for a NUance!![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")
[/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!
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!
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![[ponder]](/data/assets/smilies/ponder.gif "[ponder] [ponder]")
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
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
- Thread starter
- #7
SkipVought
Programmer
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!
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!
- Status
Similar threads
