Transposing Data and Splitting Cells

[Darko_Giac]

Hi all,

I'm currently working with a table of student scores for different tests (denoted in columns A2 through E2) that I need to transpose and assign certain values to certain numbers.

Essentially, I have one column in a table that reads 0-7 (Cell A3) and another value in column F3 that indicates the number to be assigned to that range.

I would need a way to transpose cell A3 so it splits into single digits from 0 through 7 (as opposed to being a range in one cell). I would then need to assign a 1 to that spread of numbers 0 through 7.

In cell A4, the range is 8-10, and again in a separate worksheet, or wherever is easiest, I would need to have 8,9,10 listed in separate cells, and have that range be assigned a 2.

I've attached a workbook to clarify the problem and my desired outcome, just wondering what the best way to go about this would be. I've tried VLOOKUP and INDEXMATCH, but the issue is splitting the ranges in the single cells in columns A through E.

I'm currently working on Excel Office Home and Student 2021 for Mac OS.

Any guidance on this would be greatly appreciated!

[sandy666]

would be fine if you update your profile about a three letters: Mac

[sandy666]

Do you have Power Query on your Mac in H&S XL2021 ?

here is the result via Power Query

Type	Score1	Score2
AA	0	1
AA	1	1
AA	2	1
AA	3	1
AA	4	1
AA	5	1
AA	6	1
AA	7	1
AA	8	2
AA	9	2
AA	10	2
AA	11	3
AA	12	3
AA	13	3
AA	14	3
AA	15	4
AA	16	4
AA	17	4
AA	18	4
AA	19	4
AA	20	5
AA	21	5
AA	22	5
AA	23	5
AA	24	5
AA	25	6
AA	26	6
AA	27	6
AA	28	6
AA	29	6
AA	30	7
AA	31	7
AA	32	7
AA	33	7
AA	34	7
AA	35	7
AA	36	8
AA	37	8
AA	38	8
AA	39	8
AA	40	8
AA	41	9
AA	42	9
AA	43	9
AA	44	9
AA	45	10
AA	46	10
AA	47	10
AA	48	10
AA	49	11
AA	50	11
AA	51	11
AA	52	11
AA	53	11
AA	54	12
AA	55	12
AA	56	12
AA	57	13
AA	58	13
AA	59	14
AA	60	14
AA	61	15
AA	62	15
AA	63	16
AA	64	17
AA	65	18
AA	66	19
BB	0	1
BB	1	1
BB	2	1
BB	3	1
BB	4	1
BB	5	1
BB	6	2
BB	7	2
BB	8	3
BB	9	3
BB	10	4
BB	11	4
BB	12	5
BB	13	5
BB	14	6
BB	15	6
BB	16	7
BB	17	7
BB	18	7
BB	19	8
BB	20	8
BB	21	9
BB	22	9
BB	23	10
BB	24	11
BB	25	11
BB	26	12
BB	27	13
BB	28	13
BB	29	14
BB	30	15
BB	31	16
BB	32	17
BB	33	18
BB	34	19
BB	35	19
BB	36	19

[Darko_Giac]

Do you have Power Query on your Mac in H&S XL2021 ?

here is the result via Power Query

Type	Score1	Score2
AA	0	1
AA	1	1
AA	2	1
AA	3	1
AA	4	1
AA	5	1
AA	6	1
AA	7	1
AA	8	2
AA	9	2
AA	10	2
AA	11	3
AA	12	3
AA	13	3
AA	14	3
AA	15	4
AA	16	4
AA	17	4
AA	18	4
AA	19	4
AA	20	5
AA	21	5
AA	22	5
AA	23	5
AA	24	5
AA	25	6
AA	26	6
AA	27	6
AA	28	6
AA	29	6
AA	30	7
AA	31	7
AA	32	7
AA	33	7
AA	34	7
AA	35	7
AA	36	8
AA	37	8
AA	38	8
AA	39	8
AA	40	8
AA	41	9
AA	42	9
AA	43	9
AA	44	9
AA	45	10
AA	46	10
AA	47	10
AA	48	10
AA	49	11
AA	50	11
AA	51	11
AA	52	11
AA	53	11
AA	54	12
AA	55	12
AA	56	12
AA	57	13
AA	58	13
AA	59	14
AA	60	14
AA	61	15
AA	62	15
AA	63	16
AA	64	17
AA	65	18
AA	66	19
BB	0	1
BB	1	1
BB	2	1
BB	3	1
BB	4	1
BB	5	1
BB	6	2
BB	7	2
BB	8	3
BB	9	3
BB	10	4
BB	11	4
BB	12	5
BB	13	5
BB	14	6
BB	15	6
BB	16	7
BB	17	7
BB	18	7
BB	19	8
BB	20	8
BB	21	9
BB	22	9
BB	23	10
BB	24	11
BB	25	11
BB	26	12
BB	27	13
BB	28	13
BB	29	14
BB	30	15
BB	31	16
BB	32	17
BB	33	18
BB	34	19
BB	35	19
BB	36	19

Oh I know PQ would great for tackling a task like this, but unfortunately I don't have Power Query on my Mac

[sandy666]

don't quote whole post, it is not necessary


too bad you don't have PQ because it's simple, easy and fast
so good luck and have a nice day

[Darko_Giac]

Thanks! Just out of curiosity, it's been some time since I've worked with PQ, what steps did you take to transpose this via PQ?

[sandy666]

here is your (with a bit modified ranges) example
if you have the ability to open Power Query Editor you can see whole M

here is for AA

Please Login or Register  to view this content.

for BB is exactly the same and on the end Append both tables

[Darko_Giac]

Perfect. Thank you!

[sandy666]

in short, the problem is solved, so...

---

if the problem is solved, make me happy and hit Add Reputation (bottom left corner next to the post that was helpful) (if you did it - ignore this)