Change layout of data based on conditions

Swissmiss · Dec 14, 2018

I have test data that comes in like the original file format example for each student, with their ID number in column A, question number in column B, and answers in column C. In order to upload the file I need to get it in the following format:

ID,answer,answer,answer,etc..

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

1

ID

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

2

12345

A

B

C

D

A

B

C

D

A

B

D

C

A

B

A

3

54321

B

A

C

A

B

A

B

C

D

A

B

D

C

D

B

A

B

C

A

D

I know it's some kind of lookup/match formula, but I don't know how to put it all together. Like match if the id number and question 1, then put the answer? Any help would be super appreciated! TIA

Original Data File Format

	A	B	C
1	ID	Question	Answers
2	12345	1	A
3	12345	2	B
4	12345	3	C
5	12345	4	D
6	12345	5	D
7	12345	6	C
8	12345	7	D
9	12345	8	A
10	12345	9	B
11	12345	10	B
12	12345	11	C
13	12345	12	D
14	12345	13	A
15	12345	14	B
16	12345	15	C
17	12345	16	D
18	12345	17	D
19	12345	18	C
20	12345	19	D
21	12345	20	A
22	12345	21	B
23	12345	22	B
24	12345	23	C
25	12345	24	D
1	54321	1	A
2	54321	2	B
3	54321	3	C
4	54321	4	D
5	54321	5	D
6	54321	6	C
7	54321	7	D
8	54321	8	A
9	54321	9	B
10	54321	10	B
11	54321	11	C
12	54321	12	D
13	54321	13	A
14	54321	14	B
15	54321	15	C
16	54321	16	D
17	54321	17	D
18	54321	18	C
19	54321	19	D
20	54321	20	A
21	54321	21	B
22	54321	22	B
23	54321	23	C
24	54321	24	D

gaz_chops · Dec 14, 2018

Try

=INDEX($C$2:$C$27,MATCH($E2&F$1,$A$2:$A$27&$B$2:$B$27,0))

Enter as an array, Ctrl Shift & Enter

Code:

[TABLE="width: 1653"]
<tbody>[TR]
[TD="class: xl63, width: 87"]ID[/TD]
[TD="class: xl63, width: 87"]Question[/TD]
[TD="class: xl63, width: 87"]Answers[/TD]
[TD="width: 87"][/TD]
[TD="class: xl63, width: 87"]ID[/TD]
[TD="class: xl63, width: 87, align: right"]1[/TD]
[TD="class: xl63, width: 87, align: right"]2[/TD]
[TD="class: xl63, width: 87, align: right"]3[/TD]
[TD="class: xl63, width: 87, align: right"]4[/TD]
[TD="class: xl63, width: 87, align: right"]5[/TD]
[TD="class: xl63, width: 87, align: right"]6[/TD]
[TD="class: xl63, width: 87, align: right"]7[/TD]
[TD="class: xl63, width: 87, align: right"]8[/TD]
[TD="class: xl63, width: 87, align: right"]9[/TD]
[TD="class: xl63, width: 87, align: right"]10[/TD]
[TD="class: xl63, width: 87, align: right"]11[/TD]
[TD="class: xl63, width: 87, align: right"]12[/TD]
[TD="class: xl63, width: 87, align: right"]13[/TD]
[TD="class: xl63, width: 87, align: right"]14[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12345[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"]A[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]12345[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]D[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12345[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"]B[/TD]
[TD][/TD]
[TD="class: xl63, align: right"]54321[/TD]
[TD]B[/TD]
[TD]A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12345[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63"]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12345[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63"]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12345[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63"]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12345[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl63"]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12345[/TD]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl63"]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12345[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63"]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12345[/TD]
[TD="class: xl63, align: right"]9[/TD]
[TD="class: xl63"]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12345[/TD]
[TD="class: xl63, align: right"]10[/TD]
[TD="class: xl63"]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12345[/TD]
[TD="class: xl63, align: right"]11[/TD]
[TD="class: xl63"]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12345[/TD]
[TD="class: xl63, align: right"]12[/TD]
[TD="class: xl63"]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12345[/TD]
[TD="class: xl63, align: right"]13[/TD]
[TD="class: xl63"]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12345[/TD]
[TD="class: xl63, align: right"]14[/TD]
[TD="class: xl63"]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12345[/TD]
[TD="class: xl63, align: right"]15[/TD]
[TD="class: xl63"]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12345[/TD]
[TD="class: xl63, align: right"]16[/TD]
[TD="class: xl63"]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12345[/TD]
[TD="class: xl63, align: right"]17[/TD]
[TD="class: xl63"]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12345[/TD]
[TD="class: xl63, align: right"]18[/TD]
[TD="class: xl63"]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12345[/TD]
[TD="class: xl63, align: right"]19[/TD]
[TD="class: xl63"]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12345[/TD]
[TD="class: xl63, align: right"]20[/TD]
[TD="class: xl63"]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12345[/TD]
[TD="class: xl63, align: right"]21[/TD]
[TD="class: xl63"]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12345[/TD]
[TD="class: xl63, align: right"]22[/TD]
[TD="class: xl63"]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12345[/TD]
[TD="class: xl63, align: right"]23[/TD]
[TD="class: xl63"]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]12345[/TD]
[TD="class: xl63, align: right"]24[/TD]
[TD="class: xl63"]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]54321[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63"]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]54321[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63"]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Swissmiss · Dec 14, 2018

Thank you! Okay- I'm trying to make this work, but when I put the formula in I get a value of 0. Which cell am I supposed to put the formula in? Would it be possible to put this formula in a second sheet and reference the first sheet so that I only see the second format?

Thanks for help!!

gaz_chops · Dec 14, 2018

In my sample, your List of data is in Cols A B C, I created the table on the right with 2 ID's (in Col E) as a sample & the Question No's across the top (starting in Col F). The formula is in cell F2.

does that help?

And yes you table with results/formula's can be on another sheet.

sandy666 · Dec 14, 2018

your result table is not correct with your source table

result
ID 1
12345 A
54321 B

source
12345 1 A
54321 1 A

so with PowerQuery:

ID	Question	Answers	ID	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24
12345	1	A	12345	A	B	C	D	D	C	D	A	B	B	C	D	A	B	C	D	D	C	D	A	B	B	C	D
12345	2	B	54321	A	B	C	D	D	C	D	A	B	B	C	D	A	B	C	D	D	C	D	A	B	B	C	D
12345	3	C
12345	4	D
12345	5	D
12345	6	C
12345	7	D
12345	8	A
12345	9	B
12345	10	B
12345	11	C
12345	12	D
12345	13	A
12345	14	B
12345	15	C
12345	16	D
12345	17	D
12345	18	C
12345	19	D
12345	20	A
12345	21	B
12345	22	B
12345	23	C
12345	24	D
54321	1	A
54321	2	B
54321	3	C
54321	4	D
54321	5	D
54321	6	C
54321	7	D
54321	8	A
54321	9	B
54321	10	B
54321	11	C
54321	12	D
54321	13	A
54321	14	B
54321	15	C
54321	16	D
54321	17	D
54321	18	C
54321	19	D
54321	20	A
54321	21	B
54321	22	B
54321	23	C
54321	24	D

Code:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(Source, {{"Question", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(Source, {{"Question", type text}}, "en-GB")[Question]), "Question", "Answers")
in
    #"Pivoted Column"[/SIZE]

Fluff · Dec 14, 2018

With formulae

Original data like

Excel 2013/2016

A

B

C

1

ID

Question

Answers

2

12345

1

A

3

12345

2

B

4

12345

3

C

5

12345

4

D

6

12345

5

D

26

54321

1

A

27

54321

2

B

28

54321

3

C

49

54321

24

D

Sheet1

In Sheet2

Excel 2013/2016

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

1

ID

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

2

12345

A

B

C

D

C

D

A

B

C

D

A

B

C

D

C

D

A

B

C

D

3

54321

A

B

C

D

C

D

A

B

C

D

A

B

C

D

C

D

A

B

C

D

Sheet2

Cell Formulas
Range	Formula
A2	{=INDEX(Sheet1!$A$2:$A$49,MATCH(0,COUNTIF($A$1:$A1,Sheet1!$A$2:$A$49),0))}
B1	{=INDEX(Sheet1!$B$2:$B$49,MATCH(0,COUNTIF($A$1:A$1,Sheet1!$B$2:$B$49),0))}
B2	{=INDEX(Sheet1!$C$2:$C$49,MATCH($A2&B$1,Sheet1!$A$2:$A$49&Sheet1!$B$2:$B$49,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Fluff · Dec 14, 2018

Formula in A2 copy down as far as needed
Formula in B1 copy across as far as needed
Formula in B2 copy across & down

Swissmiss · Jan 16, 2019

Thank you so much for your help! Got it to work

Fluff · Jan 16, 2019

Not sure who you're talking too, but glad we could help & thanks for the feedback

Change layout of data based on conditions

Swissmiss

New Member

Excel Facts

gaz_chops

Well-known Member

Swissmiss

New Member

gaz_chops

Well-known Member

sandy666

Banned - Rules violations

Fluff

MrExcel MVP, Moderator

Fluff

MrExcel MVP, Moderator

Swissmiss

New Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Change layout of data based on conditions

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

Banned - Rules violations

MrExcel MVP, Moderator

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock