In a table: Include data in columns into existing rows

andreslizcano

New Member
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)

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.

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.

pplstuff

Well-known Member
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.

MickG

MrExcel MVP
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

andreslizcano

New Member
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!

pplstuff

Well-known Member
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.

Replies
3
Views
424
Replies
4
Views
303
Replies
0
Views
480
Replies
3
Views
322
Replies
4
Views
551

1,195,668
Messages
6,011,056
Members
441,580
Latest member
BornholmerBjarne

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?

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

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