PDA

View Full Version : Solved: Cell Arrangement


kurt0101
09-30-2007, 05:51 PM
Hi. I am new to this forum. I have an organizational problem. The details of the problem and how I would like the data to be arranged is written in the attached excel file.
I hope someone can help me. I have a feeling I can use VLOOKUP to solve this problem but I am not sure and wouldn't know how anyway. I have tried and can't get it to work.
Thank you in advance!
anandbohra
09-30-2007, 10:49 PM
:hi: kurt0101

I had solved your query through PIVOT TABLE
kurt0101
10-01-2007, 08:14 AM
Thank you for your response. I am not 100% sure how to get your exact pivot table? i can only get column "A" but i cant reproduce the other columns.
are you using a different "layout" than the default?
please let me know

also, pivot tables are going to take a very long time to create.
without getting into too much detail i would have to do this 9-10 times for each worksheet i have and i have thousands of worksheets i need to do this for.

i have attached a sample of what i did using IF statements and it is VERY close to what i need.
the only problem is i have extra rows.
see the attached excel file for details.

thank you!!!!!!!
anandbohra
10-02-2007, 10:26 PM
hi kurt0101

there is another method of solving your problem with inbuilt function of sumproduct which i had done for you

first of all find N/A & replace with them 0 to proceed with this method

steps are first take the unique of columns A in column F (after your genuine data) now u get only single entry of column A in your Column F (represent Col A) now in Column G (which represent col B ) use this formula
=SUMPRODUCT(MAX(($A$2:$A$65536=F2)*($B$2:$B$65536)))
In col H (which represent Col C) use this formula
=SUMPRODUCT(MAX(($A$2:$A$65536=F2)*($C$2:$C$65536)))
In col I (which represent Col D) use this formula
=SUMPRODUCT(MAX(($A$2:$A$65536=F2)*($D$2:$d$65536)))

the pivot query i put in doc file as steps of what i done by taking print screen hope now u get the same.


the doc file i can not attach on this forum but uploaded somewhere else u can get it from

http://rapidshare.com/files/59907115/Pivot_Steps.doc