Merge Rows of serial numbers if Item Number is the same

[benchris]

I have a scenario I am not sure how to accomplish what I need to accomplish. I have Item numbers in column A and serial numbers for that item number in Columns B-E. Column A will have duplicate values, which I would like to have them all in the same row. For if Item 1 is in A1, A7, A12, A17 and A22, then all of the serial numbers for those rows would need to go to Cells Behind A. So in essence I would like to merge the data that each item number has into one row as shown. Any ideas on how to accomplish this? So you can see all of the serial numbers for Item 1 would go into the same row. After the forumula is run, preferably in a new sheet there would be 5 rows for each item and then all of those serial numbers for that item behind. I would then transpose the rows into columns when I am done as I need the items in columns rather than rows. Let me know if you have any ideas on how I can accomplish this.

Item Number

Serial

Serial

Serial

Serial

Item 1

1​

22​

43​

64​

6​

27​

48​

69​

11​

32​

53​

74​

16​

37​

58​

79​

21​

42​

63​

84​

Item 2

2​

23​

44​

65​

Item 3

3​

24​

45​

66​

Item 4

4​

25​

46​

67​

Item 5

5​

26​

47​

68​

Item 1

6​

27​

48​

69​

Item 2

7​

28​

49​

70​

Item 3

8​

29​

50​

71​

Item 4

9​

30​

51​

72​

Item 5

10​

31​

52​

73​

Item 1

11​

32​

53​

74​

Item 2

12​

33​

54​

75​

Item 3

13​

34​

55​

76​

Item 4

14​

35​

56​

77​

Item 5

15​

36​

57​

78​

Item 1

16​

37​

58​

79​

Item 2

17​

38​

59​

80​

Item 3

18​

39​

60​

81​

Item 4

19​

40​

61​

82​

Item 5

20​

41​

62​

83​

Item 1

21​

42​

63​

84​

After everything is done I would like to have the table look like this with all of the serial numbers behind the item number:

Item Number

Serial

Serial

Serial

Serial

Item 1

1​

22​

43​

64​

6​

27​

48​

69​

11​

32​

53​

74​

16​

37​

58​

79​

21​

42​

63​

84​

Item 2

2​

23​

44​

65​

7​

28​

49​

70​

12​

33​

54​

75​

17​

38​

59​

80​

Item 3

3​

24​

45​

66​

8​

29​

50​

71​

13​

34​

55​

76​

18​

39​

60​

81​

Item 4

4​

25​

46​

67​

9​

30​

51​

72​

14​

35​

56​

77​

19​

40​

61​

82​

Item 5

5​

26​

47​

68​

10​

31​

52​

73​

15​

36​

57​

78​

20​

41​

62​

83​

 

[lrobbo314]

Using Power Query...

Book1
	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	Z	AA	AB	AC	AD	AE	AF	AG
1	Item Number	Serial1	Serial2	Serial3	Serial4	Serial5	Serial6	Serial7	Serial8	Serial9	Serial10	Serial11	Serial12	Serial13	Serial14	Serial15	Serial16	Serial17	Serial18	Serial19	Serial20
2	Item 1	1	22	43	64	6	27	48	69	11	32	53	74	16	37	58	79	21	42	63	84
3	Item 2	2	23	44	65
4	Item 3	3	24	45	66
5	Item 4	4	25	46	67
6	Item 5	5	26	47	68
7	Item 1	6	27	48	69
8	Item 2	7	28	49	70
9	Item 3	8	29	50	71
10	Item 4	9	30	51	72
11	Item 5	10	31	52	73
12	Item 1	11	32	53	74
13	Item 2	12	33	54	75
14	Item 3	13	34	55	76
15	Item 4	14	35	56	77
16	Item 5	15	36	57	78
17	Item 1	16	37	58	79
18	Item 2	17	38	59	80
19	Item 3	18	39	60	81
20	Item 4	19	40	61	82
21	Item 5	20	41	62	83
22
23
24	Item Number	Count.1	Count.2	Count.3	Count.4	Count.5	Count.6	Count.7	Count.8	Count.9	Count.10	Count.11	Count.12	Count.13	Count.14	Count.15	Count.16	Count.17	Count.18	Count.19	Count.20	Count.21	Count.22	Count.23	Count.24	Count.25	Count.26	Count.27	Count.28	Count.29	Count.30	Count.31	Count.32
25	Item 1	1	22	43	64	6	27	48	69	11	32	53	74	16	37	58	79	21	42	63	84	6	27	48	69	11	32	53	74	16	37	58	79
26	Item 2	2	23	44	65	7	28	49	70	12	33	54	75	17	38	59	80
27	Item 3	3	24	45	66	8	29	50	71	13	34	55	76	18	39	60	81
28	Item 4	4	25	46	67	9	30	51	72	14	35	56	77	19	40	61	82
29	Item 5	5	26	47	68	10	31	52	73	15	36	57	78	20	41	62	83
Sheet7

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Item Number"}, {{"Count", each _, type table [Item Number=nullable text, Serial1=nullable text, Serial2=nullable text, Serial3=nullable text, Serial4=nullable text, Serial5=nullable text, Serial6=nullable text, Serial7=nullable text, Serial8=nullable text, Serial9=nullable text, Serial10=nullable text, Serial11=nullable text, Serial12=nullable text, Serial13=nullable text, Serial14=nullable text, Serial15=nullable text, Serial16=nullable text, Serial17=nullable text, Serial18=nullable text, Serial19=nullable text, Serial20=nullable text]}}),
    UP = Table.TransformColumns(Group,{{"Count", each Text.Combine(Table.UnpivotOtherColumns(_, {"Item Number"}, "Attribute", "Value")[Value],",")}}),
    Split = Table.SplitColumn(UP, "Count", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv))
in
    Split

 

[benchris]

Thanks, I have never used Power Query but will get that figured out! Appreciate the response!

 

[lrobbo314]

If you have the Office 365 version of Excel you can do it with formulas like this.

Book1
	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	Z	AA	AB	AC	AD	AE	AF	AG
1	Item
2	Item 1	1	22	43	64	6	27	48	69	11	32	53	74	16	37	58	79	21	42	63	84
3	Item 2	2	23	44	65
4	Item 3	3	24	45	66
5	Item 4	4	25	46	67
6	Item 5	5	26	47	68
7	Item 1	6	27	48	69
8	Item 2	7	28	49	70
9	Item 3	8	29	50	71
10	Item 4	9	30	51	72
11	Item 5	10	31	52	73
12	Item 1	11	32	53	74
13	Item 2	12	33	54	75
14	Item 3	13	34	55	76
15	Item 4	14	35	56	77
16	Item 5	15	36	57	78
17	Item 1	16	37	58	79
18	Item 2	17	38	59	80
19	Item 3	18	39	60	81
20	Item 4	19	40	61	82
21	Item 5	20	41	62	83
22
23	Item 1	1	22	43	64	6	27	48	69	11	32	53	74	16	37	58	79	21	42	63	84	6	27	48	69	11	32	53	74	16	37	58	79
24	Item 2	2	23	44	65	7	28	49	70	12	33	54	75	17	38	59	80
25	Item 3	3	24	45	66	8	29	50	71	13	34	55	76	18	39	60	81
26	Item 4	4	25	46	67	9	30	51	72	14	35	56	77	19	40	61	82
27	Item 5	5	26	47	68	10	31	52	73	15	36	57	78	20	41	62	83
Sheet8

Cell Formulas
Range	Formula
A23:A27	A23	=UNIQUE(A2:A21)
B23:AG23,B24:Q27	B23	=TRANSPOSE(FILTERXML("<a><b>" & TEXTJOIN("</b><b>",1,IF(($A$2:$A$21=A23)*($B$2:$U$21<>""),$B$2:$U$21,""))&"</b></a>","//b"))
Dynamic array formulas.

 

[benchris]

I just have 2016 and 2019 versions.