Cross-table into flat list

[Arnodekkers1995]

Dear all,


I'm trying to turn a "cross-table" into a flat list, but even after i lot of searching and reading i haven't yet achieved what i want.


First i'll give some context:


Let say table A is a cross-table, with columns which describe a cabinets' width, and with rows describing the cabinets' type. (In this example low, medium and high cabinets)


Now, in this table each customer fills in the desired number he wants to buy.


These data should be converted to a flat list, so further in the excel sheet, a list can be made (let's say as a quotation) in which the different combinations can be given a price (automatically)


Now, i will upload a picture of the file i'm using as example.


I hope you can help me turning the pivot table into a list (Dynamically)


Preferably without VBA (should be dummyproof when finished), but if really necesarry, it ain't a problem.




Thanks in advance,


Arno

 

[oldbrewer]

	300	400	500	600					col J				col N
low	2
med			3							300	400	500	600		row 3
high			1	4					low	125	126	127	128
									med	131	132	133	134
									high	137	138	139	140
	number		width	price
	2	low	300	250
	3	med	500	399		this is what is NEEDED
	1	high	500	139
	4	high	600	560		first price derived from	=B12*OFFSET($J$3,MATCH(C12,$J$4:$J$6,0),MATCH(D12,$K$3:$N$3,0))
col A	col B	col C
2	low	300
3	med	500		< < < < < < < < <	this is produced by this macro		easy to add the price formula in col D
1	high	500
4	high	600
						tot = 19
						For j = 2 To 4
						For k = 2 To 5
						If Cells(j, k) = "" Then GoTo 100
						tot = tot + 1
						Cells(tot, 1) = Cells(j, k)
						Cells(tot, 2) = Cells(j, 1)
						Cells(tot, 3) = Cells(1, k)
						100 Next k
						Next j
						End Sub

<colgroup><col width="64" span="18" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Arnodekkers1995]

Hmm, can you make this a bit clearer? I don't actually see what you're doing.. :/

Thanks for the help!

 

[oldbrewer]

col J to col N is a price table

top left table is starting point

mid left table merely defines what we want

bottom left table is produced by the macro

 

[Arnodekkers1995]

Hello,

I'm sorry, i don't really get i yet.. Is it possible to send me the file you used? I think that will clear it up for me..

Thanks in advance!

Arno

 

[Arnodekkers1995]

Tried to attach my file, but can't find a way to do this, so, beneath is a download link to the example file.
http://www.filedropper.com/cross-table

 

[oldbrewer]

Sorry I don't download - what is it you do not understand - is it what the macro is doing ?

 

[Arnodekkers1995]

Allright, fixed this, even without using vba!
But now, i've found a second problem... I'd like to separate the number a customer gives, like shown below.
(For further processing purposes.. Imagine buying 5 cabinets, but 2 in red, 2 in blue.. With this, it's possible to insert a colomn to define the color of each cabinet separately)

Offcourse, going from 'left bottom table' to 'right bottom table' also ain't a problem, if that's going to be the cure, i'll just hide the tab with the current table on it, no problem.

Thanks in advance!

Arno

 

[oldbrewer]

			size
	type	300	400	500	600
	a		3	2
	b	1			4
	c		1	2	3
									1	a	400
		this macro produces the itemised table				1	a	400
									1	a	400
		tot = 14							1	a	500
		For j = 6 To 8						1	a	500
		For k = 3 To 6						1	b	300
		If Cells(j, k) > 0 Then GoTo 100				1	b	600
		GoTo 50						1	b	600
		100 For n = 1 To Cells(j, k)					1	b	600
		tot = tot + 1						1	b	600
		Cells(tot, 10) = 1						1	c	400
		Cells(tot, 11) = Cells(j, 2)					1	c	500
		Cells(tot, 12) = Cells(5, k)					1	c	500
		Next n							1	c	600
		50 Next k						1	c	600
		Next j							1	c	600
		End Sub

<colgroup><col width="64" span="14" style="width:48pt"> </colgroup><tbody>
</tbody>