Sort fields for unique records that are in different columns in Excel 1

[gtroiano]

I've been given a spreadsheet that looks like this:

Id[tab]Type1Name[tab]Type1Amount[tab]Type2Name[tab]Type2Amount[tab]Type3Name[tab]Type3Amount
1[tab][tab][tab]A[tab][tab][tab][tab]1000[tab][tab][tab][tab][tab]B[tab][tab][tab][tab][tab]2000[tab][tab][tab][tab]C[tab][tab][tab][tab]500
2[tab][tab][tab]C[tab][tab][tab][tab]2500[tab][tab][tab][tab][tab]A[tab][tab][tab][tab][tab]1500[tab][tab][tab][tab]-[tab][tab][tab][tab]-
3[tab][tab][tab]A[tab][tab][tab][tab]5000[tab][tab][tab][tab][tab]C[tab][tab][tab][tab][tab]500[tab][tab][tab][tab]B[tab][tab][tab][tab]2500
4[tab][tab][tab]B[tab][tab][tab][tab]500[tab][tab][tab][tab][tab][tab]-[tab][tab][tab][tab][tab]-[tab][tab][tab][tab][tab]A[tab][tab][tab][tab]1000

*the hyphens are for blank fields

What I need to do is sort the spreadsheet so that for each unique Id, all the Type A's corresponding amounts are in one column and all the Type B's corresponding amounts are in the next column, and so on and so on.

Any ideas?

 

[AnotherHiggins]

Have a look at Pivot Tables.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[gtroiano]

Yeah, I've been messing around with pivot tables for this, but I'm not sure that I have a good enough grasp on their use. I've got about 5,000 records and 30 or so columns of data, and it gets a little crazy looking when start with the pivot tables.

Anyone know of a good tutorial online that explains how pivot tables can help me achieve sorting I need?

Thanks for the help!

 

[SkipVought]

Hi,

First of all, your data is in a louzy format to do ANY data analysis or processing.

Normalize your data faq68-5287.

THEN it will be a relatively simple task to SUMMARIZE you data using the PivotTable Wizard.

Right now, it is a nightmare!!!

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[yogia]

Hi gtroiano:

Let me see if I understood you correctly ...

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[gtroiano]

skip:
The data is definitely a mess, but if it weren't I guess there wouldn't be a problem to begin with. I'm looking over that FAQ right now. I hope it helps.

yogi:
Not quite. The calculations aren't the problem right now. Rather that try and explain it, this is what I'm looking to reorder everything so it looks like this:

Thanks for all the help!

 

[bluedragon2]

Add the following 6 columns:
Type1Name Type1Amount Type2Name Type2Amount Type3Name Type3Amount

in the new Type1Name put:

=IF(OR(B2="A",B4="A",B6="A"),A,"") and copy down.

in the new Type1Amount put:

=IF(B2="A",B3,IF(B4="A",B5,IF(B6="A",B7,""))) and copy down

Do the same for the other four columns but change the A to B and C...

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[gtroiano]

bluedragon2:
I'm not sure I follow. Below is what the spreadsheet actually looks like. The above A, B, C version was just an example. It wouldn't fit, but the spreadsheet below actually has an additional 14 columns. The columns go from awardid1|awardamt1 to awardsid12|awardamt12. In total there are 15 different award types (PROV-C, PELL, NDSL, eetc.), and about 5,000 records.

I'm not sure if this negates your answer...if it doesn't, then I just don't understand what you were trying to do with those formulas, because they didn't do what I needed them to do.

Thanks again, everybody!

 

[AnotherHiggins]

gtroiano:

I'd like to second Skip's suggestion that you straighten up your data. If it was in a proper table format, you would be able to slice and dice it any way you want in a matter of seconds.

I understand that the FAQ might look intimidating, and you'd rather get a quick work-around, but you'll be much better off in the long run if you put the time into fixing your table structure now.

If you have any specific questions about the FAQ, post back and we'll help you out.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[yogia]

Below is what the spreadsheet actually looks like. The above A, B, C version was just an example. It wouldn't fit, but the spreadsheet below actually has an additional 14 columns. The columns go from awardid1|awardamt1 to awardsid12|awardamt12. In total there are 15 different award types (PROV-C, PELL, NDSL, eetc.), and about 5,000 records.

Hi gtroiano:

For the data that you have posted, please show us what will be your expected results and how are the results laid out ... and then let us take it from there.


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[AnotherHiggins]

gtroiano,

Allow me to reiterate. You'll be better off in the long run if you fix the way your data is stored now. I'm sure that a workaround is possible. That doesn't mean it is the best way to proceed.

An example of what your data will look like after you normalize it:
[TT]
ID Award AwardAmt
1 PROV-C 2500
1 STAF-U 2625
2 PROV-C 2500
2 FWSP 2000
2 STAF-S 2625
3 ACADEM 15220
3 PROV-C 2500
3 STAF-U 2625
4 STAF-U 2625
5 PROV-C 2500
5 NDSL 2000
5 FWSP 2000
5 STAF-S 2625
[/TT]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[gtroiano]

John:
This spreadsheet is just the output from some database that I have no control over, so normalizing the db isn't really an option. If normalizing the data in the spreadsheet will help me achieve the following, I'm all for it of course. Though doing it is another matter altogether...

Yogi:
Here's what I'm hoping to get it looking like:

*this is the same data as the first screen capture, but only the first 10 records.

 

[yogia]

Yogi:
Here's what I'm hoping to get it looking like:

Hi gtroiano:

Let us have a look at the following ...

I have used two formulas ... one in cell C22 and another in cell D22. This combination of formulas is then copied to cells C22:L31.

I hope this helps.




Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[gtroiano]

yogi:
That did it. Thanks you so much!

And thanks to everyone else who helped me along!

 

[yogia]

Hi gtroiano:

You Are Very Welcome ... now let us keep EXCELing.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com