Making multiple tables in one sheet into one table in one sheet

[jamie_bisson]

What I am looking to do is to reformat a table in excel.

When I do a data export from a specific website it gives me lots of data that is split into many "tables" within the same sheet, for example the first row "A1" is repeated many times as I scroll down the page. I have given an example below and changed column A into A1, A2, A3 etc as we move down the rows, column B is ingredients.

What I want to do is make the first column goto a row. I know I can do this with a copy and special paste (transpose), but surely I don't have to do this with every instance of the data in each "table" for the second column.

Example:
A1 Butter
A2 Milk
A3 Pears

A1 Mangoes
A2 Apples
A3 Potatoes

There are over 1000 of these A1-A3 examples in my real data sheet.

Any ideas?

[MarMo]

Pls upload a small sample WB indicating the problem. Also add the solution expected.

[sandy666]

you can try PowerQuery, join all tables into one in PQ editor then load this query table to the sheet

that's all what I can say without any example excel file

[jamie_bisson]

Ok, feel really stupid here.... don't shoot me down, how do I insert an excel table in here???

[sandy666]

Attach example EXCEL file (not a picture, pasted text or any other than Excel format!). This file should be attached to a new post. Never change your original post (except admin's request).

To attach an Excel file to your post,

• desensitize data
• remeber that your example should reflect structure and type of data and contain the result what you want to achieve (manually created if necessary)
• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

[jamie_bisson]

sorry, but I just want to add an excel file to this post. I don't see go. advanced manage attachments. I just want to show someone the excel file in here.

[jamie_bisson]

OK, found the attach file button, but it won't load. I will try when I leave work and get home.

But I have now somehow managed to unsubscribe from this thread. How do I resubscribe?

[sandy666]

maybe read instruction carefully

attach.jpg

if you post anything you will be subscribed to this thread automatically

[jamie_bisson]

I think I have managed to attach the file. On the left are the multiple tables with associated data in the second column. On the top right is the output I am aiming to achieve.

Sorry for the absolute stupidity, as I couldn't see the "Go Advanced" button.

There are over 4000 instances of Item 1 - 8

[sandy666]

Done with PowerQuery, is that what you want?

Power Query for

• Excel 2010 Pro Plus or Excel 2013 Pro Plus can be downloaded as free add-in from MS site (choose correct version). I suggest Power Pivot also
• Excel 2016 or Excel 365 is built-in, aka Get&Transform
• PowerQuery works with M-language and does not contain any classic formulae or vba code
• What PowerQuery is? see here: Getting Started with PowerQuery (Get&Transform) in Excel

[jamie_bisson]

Thanks for that. Now I just need to work out all the steps by inspecting the editor. I have worked out how to do this, but it may be a challenge.

Question: In the step called "Added custom", did you just manually enter the M code, or whatever it's called? How on earth am I supposed to know that coding????

Also I think that the scripting is not exactly correct as I had 8 items with 5 in each item. Your table has 5 in 4 items and sometimes 4 or 3 in others. Is there a scripting error, as I have no idea how to manually script for my needs of what I need to do.

Again, many thanks as I can copy what you have done, but I need to ensure the output of the scripting is accurate as my data is massive and I may not be able to absolutely notice discrepancies easily.

[sandy666]

Question: In the step called "Added custom", did you just manually enter the M code, or whatever it's called? How on earth am I supposed to know that coding?

You need to learn M code How on the earth you know Excel function: SUM(A1,B1)???

Also I think that the scripting is not exactly correct

Because duplicates was removed

So maybe try to show little example with before and after what you really need

edit:
here is with duplicates

Item 1	Item 2	Item 3	Item 4	Item 5	Item 6	Item 7	Item 8
Ankle	Apple	Ankle	Apple	Grape	Fromage	Cheese	Apples
Apples	Head	Frogs	Head	Leg	Fromage	Cheese	Foot
Hammer	Maple	Fromage	Nuts	Maple	Fromage	Pineapple	Nuts
Leg	Pears	Pears	Screwdriver	Maple	Mushroom	Pineapple	Tomato
Screwdriver	Piano	Piano	Tomato	Pear	Pear	Tennis	Tomato

Get it or forget it