Transpose Data automatically

[Dave32]

My data looks like this: (each cell contains 1 item of data)

Apple
15
1
2
3
<blank cell>
Pear
1
3
4
7
<blank cell>

Etc... for 100's of line. So name of fruit then calorific values underneath

I need the data to look like this:

Apple 15 1 2 3
Pear 1 3 4 7

Etc..

Then I can apply Vlookup functions, so if your enter a food name you get all the relevant data.

Dave32

[StephenR]

You should mention when you cross-post so as not to waste people's time.

http://www.ozgrid.com/forum/showthread.php?t=86474

[Dave32]

Sorry, I didn't realise I was on the same website.

[VBA Noob]

Your not on the same website. We just police cross posters. Read forum rules and message to cross posters below to understand why

VBA Noob

[Dave32]

Message understood loud and clear. This will not happen again. I am new to this forum stuff.

Dave32

[sweep]

Hi,

Not particularly neat and tidy, but this will do the job. Alter the 1000 below according to how many rows you have

Please Login or Register  to view this content.

[Dave32]

Dave,

Thanks for the reply. I am sorry I don't now how to use Macro's. Please help me to put this code in the right place within Excel.

Regards,

Dave32

[Dave32]

Raw data is attached. 1 Food item + 12 values.

See the "data" datasheet.

Dave thanks for the code.


Dave32

[sweep]

Dave,

no attchment.

To implement the code, oen excel, press alt & F11 to open to VB editor. Do Insert > Module, and paste the code into the window that pops up. Close the VB editor.

In excel go to tools > Macro > Run

[Dave32]

Dave,

I managed to create and run the Macro now, I changed 2 values in the formula and it went wrong:

My data is all in Col A1 to A601

Format of data is:

Food Item
1
2
3
4
5
6
7
8
9
10
11
12
<blank cell>

So 12 different value + the food item

[sweep]

Please Login or Register  to view this content.

[Dave32]

Wow, that works .... Brilliant.

Thanks you your help!!!!

Dave32

[Dave32]

Dave,

The Macro runs fine until I get to the letter E.

Then all the data moves accross 1 column

My file is 1.44MB and to big to send through this website. Can I email the file to you?

I have 11700 ish lines of source data, is that a problem?

Dave32

[sweep]

If that's happening it's because there's a list of numbers under your fruit name that's less than the 12 digits you specified. You can email it to me if you like. Take a look at your pm for address.

[Dave32]

I found the error, the Macro now runs perfectly.


The VLOOKUP formula runs OK as well. but.....


=VLOOKUP(A4,Data!$C$1:$O$837,2)

A4 = Food name
Data!c1:O837 = where the raw data is
2 is giving me the first value of the data set.

But if you enter a food item which is not in the data, then you don't get an error, you just get data randomely from another food item.

Thanks,

Dave32

[sweep]

Try this

=VLOOKUP(A4,Data!$C$1:$O$837,2,FALSE)

[The Tiger]

Hi

Thank you for interesting solution.

I have a similar task, but my problem is that the rows differ in number.
The solution above work if you have the same exact number of rows to transpose.

I have two columns;

Column A have 0 and 1 numbering.
Column B consist of ID´s.
I need it to transpose the ID`s for 1+0 items until reaching a new 1 row.

Example;

Column A , Column B
1 123
0 234
0 345
1 987
0 876
0 654
0 543
0 432
1 111
0 112
1 222
0 223
0 224

The result i´m looking for is:

Column A , Column B, Column C,D,E,F,G etc.
1 123 123,234,345
0 234
0 345
1 987 987,876,654,543,432
0 876
0 654
0 543
0 432
1 111 111,112
0 112
1 222 222,223,224
0 223
0 224


Anyone have an idea how this can be made?
A big thank you in advance!
/Stefan