PDA

View Full Version : [SOLVED] Reformat data in specific way



YasserKhalil
10-15-2016, 12:35 PM
Hello everyone
I have Data sheet with three columns one for dates second for fields and third for values
Need to have unique dates in rows and unique fields in columns (First Row) and the values to be put in the suitable cells
I could make it using pivot table but I need VBA code to achieve that and I prefer VBA arrays if possible
Thanks advanced for help

p45cal
10-15-2016, 02:42 PM
In the attached on sheet Data:
Unique dates in column E using advanced filter
Uniques fields in row 1 using advanced filter tansposed.
One formula:
=SUMPRODUCT(($B$2:$B$32=F$1)*($A$2:$A$32=$E2),$C$2:$C$32)
Ctrl+Entered into cells F2:J22 all at once while F2 was the active cell.
To hide zero values, chose the option to hide 0 in cells with 0 value for that sheet.

This I don't understand: "but I need VBA code to achieve that and I prefer VBA arrays"; what are VBA arrays if you can't use VBA code?

YasserKhalil
10-15-2016, 09:44 PM
Thanks a lot Mr. p45cal for reply
Your solution is very good and I like it ..
I need to store the whole range of data in an array then to manipulate all processes .. After all put the results in one shot in any cell as start ..
Thank you very much

YasserKhalil
10-16-2016, 12:28 PM
I appreciate any help in this topic

p45cal
10-16-2016, 04:44 PM
Click button in vicinity of D1 on Data sheet of the attached.

YasserKhalil
10-16-2016, 05:32 PM
That's fascinating Mr. p45cal
That's exactly what I was searching for
Thank you very much for great and wonderful help