Organize multiple rows and columns of data into a database format

[smd71092]

Hello all, I have some data in my Excel file that is formatted like this (the actual file is much larger but follows this exact format with an empty row in between each of the "Items")

	Customer1	Customer2	Customer3
Item1 (like/no pref/dislike)	Like	Like	Dislike
Amount of Item1 bought	27	5	1
Item2 (like/no pref/dislike)	No pref	Like	Dislike
Amount of Item2 bought	5	5	2
Item3 (like/no pref/dislike)	Dislike	No pref	Like
Amount of Item3 bought	9	1	17

<tbody>
</tbody>

Is there any formula or macro that would be able to output this into a database friendly format? What I want is to have the customer name in the first column, the item name in the second column, whether or not the customer liked/disliked or had no preference to the item in the third column and finally how many of the item they bought in the fourth column so it would look something like this.

Customer Name	Item	Like/no pref/dislike	Amount bought
Customer1	Item1	Like	27
Customer1	Item2	No pref	5
Customer1	Item3	Dislike	9
Customer2	Item1	Like	5
Customer2	Item2	Like	5
Customer2	Item3	No pref	1
Customer3	Item1	Dislike	1
Customer3	Item2	Dislike	2
Customer3	Item3	Like	17

<tbody>
</tbody>

I am familiar with the OFFSET function but couldn't think of a way for it to work here so it seems like this would have to be a macro although I would prefer a formula. Any help at all would be appreciated!

 

[MickG]

Try this for results on sheet2

[COLOR="Navy"]Sub[/COLOR] MG04Dec24
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
c = 1
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)
Lst = Cells("1", Columns.Count).End(xlToLeft).Column
ReDim ray(1 To Lst * Rng.Count, 1 To 4)
ray(1, 1) = "Customer Name": ray(2, 1) = "Item": ray(1, 3) = _
"Like/no pref/dislike": ray(1, 4) = "Amount bought"
[COLOR="Navy"]For[/COLOR] col = 1 To Lst - 1
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Offset(, col).Areas
        c = c + 1
        ray(c, 1) = Cells(1, Dn.Column)
        ray(c, 2) = Split(Cells(Dn(1).Row, 1), " ")(0)
        ray(c, 3) = Dn(1)
        ray(c, 4) = Dn(2)
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Next[/COLOR] col
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, 4)
    .Value = ray
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick