In a table: Include data in columns into existing rows

andreslizcano

New Member
Joined
Jun 5, 2012
Messages
2
Hello,

I have a table in which the rows represent cities and the columns represent certain characteristics/ attributes a city can have. In the cells every city & attribute pair receives a "grade"

City Eating Beach Ski
Barcelona 2 1 0
Chamonix 0 0 2

I would like to transform this so that every row represents a city & attribute pair and includes its corresponding grade.(I want to do this in order to be able to add more information about city- attribute pairs rather than about cities only)

City Attribute Grade
Barcelona Eating 2
Barcelona Beach 1
Barcelona Ski 0
Chamonix Eating 0
Chamonix Beach 0
Chamonix Ski 2

I am aware that I can use a pivot table to sort out all the pairs with a specific grade. and then I could paste this into a new table. But my data base (number of cities x number of attributes) is huge and I was wondering if there is no automatic process to make this transformation.

Thanks in advance
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
a1=city, d1=ski
a2=barcelona

Here's what to do, based on the references above:
1) b2=$a2&"|"&b$1 ---Make this new matrix which will be filled with "Barcelona|Eating"...

2) In a new sheet. a1=1, a2=2, a3=3...a4=a1, a5=a2, a6=a3... ---This will make a repeating pattern, you should make the number equal to the number of attributes (in your example, 3).
3) In this same sheet. b1=0, b2=0, b3=0, b4=b1+1... ---This will give you an increasing count of the cities.
3) Also in this new sheet. c1=offset(sheet1!$b$2,b1,a1) ---This pulls all the values from the first matrix you made.
4) Copy-paste special-values on sheet2!C:C
5) Use text-to-columns, set the delimiter to "|"

6) Use the same process to populate the values.

I hope this makes sense. Follow it step-by-step and you'll figure out what's happening.
 
Upvote 0
Try this for a Basic Idea !!
Your Data in columns "A to "D".
Results start "H1"
Code:
[COLOR=navy]Sub[/COLOR] MG07Jun59
[COLOR=navy]Dim[/COLOR] Rng     [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn      [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] C       [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Ac      [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.count).End(xlUp))
ReDim Ray(1 To Rng.count * 4, 1 To 3)
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
        [COLOR=navy]For[/COLOR] Ac = 1 To 3
            C = C + 1
            Ray(C, 1) = Dn: Ray(C, 2) = Rng(, Ac + 1).Offset(-1): Ray(C, 3) = Dn.Offset(, Ac)
        [COLOR=navy]Next[/COLOR] Ac
[COLOR=navy]Next[/COLOR] Dn
Range("H1").Resize(UBound(Ray), 3) = Ray
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
 
Upvote 0
Hey pplstuff,

Thanks a lot, with a couple of changes it worked out! You were right: initially I had no idea what I was doing!

Thank you, too, MickG.I didn't knew how to use the code, though and a colleague told me that it doesn't work on Mac.

Have a good day!
 
Upvote 0
Glad we could help!

My suggestion will work -- but, if this is something you'll be doing frequently, I'd suggest a macro as this is a few steps and very time consuming.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top