Excel data organization nightmare

[blumfeld0]

hi! I have a difficult data organization problem and I will do my best to explain it so please bear with me. I really hope someone can help me resolve this as i need to do this for work everyday starting tomorrow.

I receive data organized like so:
Column A contains numbers ranging from 2000-30000 in random increments in ascending order.
Column B contains the letters C and P (corresponding to the numbers in column A) in random order.
Column C contains just some random numbers corresponding to columns A and B.

e.g.
Column A Column B-Type Column C
3000 C 5560
5000 C 3560
5400 P 1
5600 P 1
5800 P 1
6000 C 2560
6000 P 2

etc. etc. for many thousands of cells.




How I need the (above) data organized:
Column A Column B-Type C Column C-Type P
3000 5560 N/A
5000 3560 N/A
5400 N/A 1
5600 N/A 1
5800 N/A 1
6000 2560 2
etc. etc. for thousands of cells.

I hope this makes sense.

I know how to use the "sort" function in excel but it doesn't do this kind of sorting i dont think.

if someone can please help me i would greatly appreciate this!!! please ask me questions if something does not make sense. thank you!!

[anandbohra]

say your data is in Column A, B, C respectively
as per your example Column A will not change ok
so now user column D,E,F to get your required output
so for column D i.e. D1 formula will be =A1 which gives u 3000 (your example)
now for Col. E i.e. E1 formula will be= =IF(B1="C",C1,"N/A") which gives u 5560
& for Col F i.e. F1 formula will be = =IF(B1="P",C1,"N/A") which gives u N/A


copy all formula till u require & u get your desired output

[rbrhodes]

Hi Illinois! (and Blumfeld0)

There's a 2 hour time difference. (I'm in PDST) but here's a sample based on your example. It could do a lot more, or different or... depending on what you really need.

However, I've a feeling this could save you tons o' time! Would your work be willing to pay for that time savings or is it just you trying to get ahead? Either way, computers are supposed to save you time are they not? <g>

It's midnight:20 here and 2:20 AM where you are, I just finished a show (my work) and I'm heading home for a beer so I may not be able to help you out for your tommorrow morning but post here or email me and I'll be able to help you out - one way or the other, I think.

[rory]

Have you thought about a pivot table?

[blumfeld0]

Rory- thank you for your response.
I am not the best excel user so am not too familiar yet with pivot table. if you think this can be done with a pivot table I will absolutely try it and get back to you.


anandbohra --thank you for your response. i tried what you indicated and it is SO VERY close to working perfectly.
the only problem is when i have a situation like
Column A Column B Column C
6000 C 2560
6000 P 2
i.e when column A has two numbers (6000, in this case) that are the same back to back! so frustrating. but I have that a lot in my data.
so what happens in this case when i try your "if" statements is that I get output like this:
Column D Column E Column F
6000 2560 N/A
6000 N/A 2

But what i need the output to look like this:

Column D Column E Column F
6000 2560 2

any ideas guys how to correct this?


thank you for all your help!! very much appreciated!

[anandbohra]

hi blumfeld0
as suggested by rory the pivot will be best in your case.
i have attached the small example file
in sheet1 from column A to C paste your data
then in sheet pivot - on pivot table right click & click refresh data u get your desired output.
if not happy then in next post upload your partial data ok

but i think this will solve your purpose

[blumfeld0]

i hope I can do this with a pivot table since I am not familiar with this tool.but with your attached example it should make it a lot easier to implement for all my data. if you think of a way to do this with an IF statement or other type of statement please let me know.
thank you so much!! you all have been a big help

[blumfeld0]

So I have been able to use pivot table to reproduce your results anandbohra, thank you.
I have one final question and then this issue will be resolved hopefully. In your example anandbohra, in column A you put two numbers that were the same, namely, 6000.
what do I do if I have 6000 appearing more than 2 times?-my data has some repeated as much as 10 times! in this case, i find that when i do "sum of marks" it ADDS the "marks" instead of just putting them in a organized fashion. i dont want to add them.

ex.
6000 C 2560
6000 P 2000
6000 P 2000


pivot table outputs:
6000
C P
2560 4000


that is bad.
thank you again!

[JGobert]

Try double clicking the grey cell just over the left colomn of the Pivot Table (in the prior example it was titled No)...if the subtotals are set to automatic change them to none.. hope this helps

[blumfeld0]

JGobert--thank you for the suggestion. I set the option to "none" but it didn't work. is this surely supposed to work or am i being dumb? this could well be possible.

thank you! ill keep trying. if anyone thinks of something let me know. very much appreciate it!

[rory]

A pivot table will always perform an aggregation on your data. What would you want the data to look like?

[blumfeld0]

after reading in detail about pivot tables i have organized my data the way i wanted to. thanks for the good idea guys!

[anandbohra]

blumfeld0

this small change will give you exactly what u want







Sum of Marks No Value Total 1000 P 2458 2000 C 2460 3000 C 2160 P 2200 4000 C 2450 P 2300 5000 C 2468 6000 C 2560 P 4000

[blumfeld0]

thanks anandbohra. youre a genius! time to do a lot of dragging and organizing.

blumfeld0