Transpose a data table

[jbjtc]

Can anyone please help with the following:

I have a table as follows:

Col A Col B Col C
Room No. Code Qty
01 Unit 1
01 W/top 2
01 Sundry 3
02 Unit 5
02 W/top 1
02 Sundry 1
03 Unit 7
03 W/top 4
03 Sundry 3

I need to transpose the table so that Codes are in column A, followed by
each room no. as a column header, with qty's against each code, as follows:


Col A Col B Col C Col D
01 02 03 <--- Room No.'s
Unit 1 5 7
W/top 2 1 4
Sundry 3 1 3

I've tried loookup's and index/match functions, but to no avail.

This is very urgent, so if anyone can help, i would be very grateful.

Kind regards

 

[Joel]

The trick is to get the Dollar Signs correct. use Sumproduct

=SUMPRODUCT(--(H$1=$A$2:$A$10),--($G2=$B$2:$B$10),$C$2:$C$10)

I put the summary table in the range G1:J4. column C2:G4 had the codes and
H1:J1 had the RoomNo. I put the formula in cell H2 and then copyied it to
range H2:J4. The only problem I see is with the Number 01, 02, 03. tTey
have to be in the same format in column A as they are in H1:J1.

 

[jbjtc]

Thanx for the reply Joel

Tried setting out your formula but they just returned zero values.

Not quite sure what is happening. Can you elaborate further for me ?

Thanx

 

[jbjtc]

Joel

I managed to work through the problem using your sumproduct formula

Thank again

 

[Joel]

It is possible that the room Number are not matchingg. I'm concerned with
you posting with zeroes as the leading digit. Not sure if it is just
formated with a leading zero or there is a single quote infront of the number
making the 01 text. I said in my previous posting that the Room Numbers had
to match in the final table and the initial data.

Here is how the formula works
=SUMPRODUCT(--(H$1=$A$2:$A$10),--($G2=$B$2:$B$10),$C$2:$C$10)

Assum this formula is in cell H2 and the original table is located A1:C10
with first row a header row containing RoomNo, code, and Qty.

H$1=$A$2:$A$10 compares H$1 the first row which in my case 01 with the data
in column A. Your eample would produce an array
{01,01,01,02,02,02,03,03,03). the comparison with h$1 (01) will produce an
array of True/False (True,True,True,False,False,False,False,False,False}.
the two -- changes the True/False to 1/0. You will know have
{1,1,1,0,0,0,0,0,0}.

Now looking at $G2=$B$2:$B$10. $G2 = Unit which is compared agains column
B. You then get an array of {1,0,0,1,0,0,1,0,0}

finally you have $C$2:$C$10 which produces an array of the numbers in column
C. which is {1,2,3,5,1,1,7,4,3)

You final results is

=sumproduct({1,1,1,0,0,0,0,0,0},{1,0,0,1,0,0,1,0,0},{1,2,3,5,1,1,7,4,3))

=sumproduct({1,0,0,0,0,0,0,0,0})

The same thing can be repeated for each cell

G H I J
1 01 02 03
2 Unit 1 5 7
3 W/top 2 1 4
4 Sundry 3 1 3

 

[jbjtc]

Thanks again for your reply Joel.

I have managed to implement your code to my data table, but unfortunately,
it has slowed my macro down to a snails pace. Is there any way to speed this
up. I know that sumproducts are notorious for slowing things down. I know the
function to turn the automatic calculation off, but i'm worried the data
table won't update when new values are imported into it. Any ideas ?

Kind Regards

 

[Joel]

You should turn screen updating off while running your macro. The worksheet
functions including sumproduct won't perform the updates until screen
updating is turn on at the end of your macro.

sub test
Application.ScreenUpdating = False
'
' Your code here
'
Application.ScreenUpdating = True

end sub

 

[jbjtc]

Thanks for reply Joel, unfortunately, it didn't speed up.

Is there any way for me to copy your formula in using a macro, based on the
size of the data table, hence:

room 1 room 2 room 3 room 4 room5

unit 1 sumproducts formulae here
wtop 1
sundry 1
unit 2
w/top 2
sundry 2

In the above example, i would only need to copy sumproduct formula based on
the size of the matrix. i.e. Is there any way to do this depending on the
number of columns versus no. of rows ?
This would speed up my macro as i would not need to prefill the matrix with
formulae.

Kind Regards