How to interchange cell values with column names in a data table in Excel

I have following data of 3 persons posted at 3 places on 3 days of the week:

JRb8L.png

Please Login or Register  to view this content.

I want to convert above data to following table format:

prk2Z.png

Please Login or Register  to view this content.

The cells of new table should get filled automatically using data in first table.

How can this be done?

When asking a question and you have some sample data, it is always better to attach an actual Excel file instead of just pictures of your data. That way we can modify your file without having to create one from scratch. Put the formula in the upper left corner of the destination table and fill across and down to fill the table.

Values as displayed

	A	B	C	D	E	F	G	H	I
1	Original					Solution
2		Tom	Dik	Harry			Cafe	Library	Gym
3	Monday	Cafe	Library	Gym		Monday	Tom	Dik	Harry
4	Tuesday	Library	Gym	Cafe		Tuesday	Harry	Tom	Dik
5	Wednesday	Gym	Cafe	Library		Wednesday	Dik	Harry	Tom

Underlying formulas

	A	B	C	D	E	F	G	H	I
1	Original					Solution
2		Tom	Dik	Harry			Cafe	Library	Gym
3	Monday	Cafe	Library	Gym		Monday	=INDEX($B$2:$D$2,1,MATCH(G$2,$B3:$D3,0))	=INDEX($B$2:$D$2,1,MATCH(H$2,$B3:$D3,0))	=INDEX($B$2:$D$2,1,MATCH(I$2,$B3:$D3,0))
4	Tuesday	Library	Gym	Cafe		Tuesday	=INDEX($B$2:$D$2,1,MATCH(G$2,$B4:$D4,0))	=INDEX($B$2:$D$2,1,MATCH(H$2,$B4:$D4,0))	=INDEX($B$2:$D$2,1,MATCH(I$2,$B4:$D4,0))
5	Wednesday	Gym	Cafe	Library		Wednesday	=INDEX($B$2:$D$2,1,MATCH(G$2,$B5:$D5,0))	=INDEX($B$2:$D$2,1,MATCH(H$2,$B5:$D5,0))	=INDEX($B$2:$D$2,1,MATCH(I$2,$B5:$D5,0))

sorry, wrong post

Provide a sample workbook illustrating this extension problem.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Originally Posted by rnexcel

Will it work, if there is duplication of persons or site/location?

If the row with names has the same name more than once, you will only get the entry under the first occurrence.

If your data is more complicated than what you showed then you will need to provide a sample file.

Can pivot table or some similar technique work for this?

(I tried to add an Excel file as attachment but cannot add it to first or this post).

If you tried to use the paperclip icon, it doesn't work.
Please click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window as stated in the banner at the top of the page.
Let us know if you have any questions.

Why the old .xls format? You are using a much newer version, so you can attach an .xlsx file that everyone can open.

(From post #4) Will it work, if there is duplication of persons or site/location?

Glad that you were able to upload a file, however it doesn't illustrate the issue about which you asked above.
That said, to fill the output in the file paste the following modification of Jeff's formula into cell C14 and drag over and down:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Yes, the formula works well:

The .xls format will not open automatically for forum members when attached here - that's the main problem for us. For you, you will no doubt find that the .xls format will limit what is supported. If you are using Excel 2019, then you should be saving everything in .xlsx format.

(From post #12) Yes, the formula works well

Thank You for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

Originally Posted by AliGW

The .xls format will not open automatically for forum members when attached here

I don't have that problem. The .xls files (including the one above) pop open right away for me.

Originally Posted by rnexcel

What is the main advantage of xlsx over xls? Can newer version not open xls format?

The format changed starting with Excel 2007. That was 14 years ago. Very few people still use versions older than that. Therefore anybody who edits your .xls file may be using features that not supported by .xls, such as newer conditional formatting features. You will have the same problem yourself if you are using Excel 2019. You should always use the .xlsx format, and not .xls.