Transpose data

Hello,

I have been assigned an urgent task this morning and would need some help.

I have a huge table as show below;Attachment 633127

I would like to amend asAttachment 633128

I am actually placing the connection into a column and fill the correspondent details, buy using Index Match function =INDEX(FCB[depdesc],MATCH(1,(A3=FCB[code])*($D$2=FCB[source]),0) with CSE but since table contains about 150,000 row it's taking ages to complete.

Any suggestion or ideas how I could speed and improve?

Thanks in advance

Which version of Excel are you using?

What exactly have you been tasked to do? What is the transposition?

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually).

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Could you please update your user profile to show your current Excel version? If 1906 is a Mac version, then add Mac, too.

OK - thanks. Office 365 would have been enough, since it's always up-to-date!

Now for that workbook, please ...

Just seen the workbook - I cannot see the connection between the two tables. What is supposed to have happened? How does the one on the left relate to the one on the right?

How on earth are you supposed to get from the table on the left to the table on the right?? They are completely different data. If you don't give us before/after data as requested in bold/red at post 4... were playing guessing games.

But the food items are completely different ... How do you get from Lyons Rich Tea to Tabasco Sauce? They aren't even the same type of foodstuff ...

Your second sample is even more confusing.

Gawd!! I'll have just one punt at this:

=IFERROR(INDEX($J:$J,AGGREGATE(15,6,ROW($J$3:$J$11)/($G$3:$G$11=C$2),ROWS($1:1))),"")

in C3, copied across and down. I don't play guessing games and you have not done what I asked for (twice) showing before/after data in a represwntative sample file.

You can use PowerQuery (Get & Transform on the Data ribbon). You can either load the table (as I did below) or load the source data directly from a workbook file without opening it first.

M Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Reordered Columns" = Table.ReorderColumns(Source,{"code", "desc1", "Connection", "depdesc", "catdesc"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"catdesc"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Connection]), "Connection", "depdesc")
in
    #"Pivoted Column"

Result:

Excel 2016 (Windows) 32 bit

	G	H	I	J	K
13	code	desc1	PT St Venera	PT Spinola	PT St' Julians
14	072417075872	Lyons Rich Tea 300 grms	FOOD CUPBOARD	FOOD CUPBOARD	FOOD CUPBOARD
15	3185205017590	Tipiak Mini Bouchees ( Vol-au-vent ) 14	FOOD CUPBOARD	FOOD CUPBOARD	FOOD CUPBOARD
16	3185205040109	Tipiak Bouchees Patissieres Vol Au Vent	FOOD CUPBOARD	FOOD CUPBOARD	FOOD CUPBOARD
17	3245412563256	Carrefour Tortillas Messicana 8 pcs.	FOOD CUPBOARD	FOOD CUPBOARD
18	3262350211421	Forchy Raisins Cake 275 grms	FOOD CUPBOARD	FOOD CUPBOARD
19	4003587077805	Dan Cake Waffels 250 grms	FOOD CUPBOARD	FOOD CUPBOARD	FOOD CUPBOARD
20	4007933454012	Delba Organic Rye Bread 500 grms	FOOD CUPBOARD	FOOD CUPBOARD	FOOD CUPBOARD
21	4007933454029	Delba Organic Sunflower Seed Bread 500 g	FOOD CUPBOARD	FOOD CUPBOARD

Sheet: Sheet1

Are you addressing me or Glenn?

Ignore my previous formula...

In I4, copied across and down:

=IFERROR(INDEX($D$4:$D$24,INDEX(MATCH($G4&I$3,$B$4:$B$24&$A$4:$A$24,0),0)),"")

I thought you said that formulae were slowing you down?

I'll have a look and let you know.

Yes - pivot each category separately and then merge the queries - results on sheet 3 of the attached.

Did this work for you?

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Thanks for letting us know - glad to have helped.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.