Need help with a formula for transposing data

[Excellency]

Hello! I'm working on a project and need some help with a formula that can transpose data. In the example below, my data set is listed in two columns (Example 1). Ideally, I need it to be transposed so that the company information is provided in the first column, then the names for each company listed horizontally for each company (Example 2).

I can use the transpose function. However, with a larger dataset it can become pretty labor intensive. I've also attempted this using a combined XLOOKUP and OFFSET formula, but it requires a good bit of manual adjusting as well.

My hope is that someone can provide a formula or non-VBA option on this . Please review and let me know if you have any suggestions!

Example 1

Example 2

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	County	District
2	Bedfordshire	Luton		Bedfordshire	Luton
3	Berkshire	Windsor and Maidenhead		Berkshire	Windsor and Maidenhead
4	Cambridgeshire	East Cambridgeshire		Cambridgeshire	East Cambridgeshire
5	County Durham	Stockton-on-Tees		County Durham	Stockton-on-Tees
6	Derbyshire	High Peak		Derbyshire	High Peak
7	Devon	Exeter		Devon	Exeter
8	East Sussex	Brighton and Hove		East Sussex	Brighton and Hove
9	Gloucestershire	South Gloucestershire		Gloucestershire	South Gloucestershire
10	Greater London	Lambeth		Greater London	Lambeth	Waltham Forest	Brent	Haringey	Hackney	Hounslow	Newham
11	Greater London	Waltham Forest		Hampshire	Hart	Test Valley
12	Greater London	Brent		Hertfordshire	Three Rivers	East Hertfordshire
13	Greater London	Haringey		Lancashire	Wyre
14	Greater London	Hackney		Leicestershire	Hinckley and Bosworth	Leicester	Blaby
15	Greater London	Hounslow		Lincolnshire	North East Lincolnshire
16	Greater London	Newham		Nottinghamshire	Nottingham
17	Hampshire	Hart		Somerset	Bath and North East Somerset
18	Hampshire	Test Valley		South Yorkshire	Sheffield
19	Hertfordshire	Three Rivers		Staffordshire	Stafford	Staffordshire Moorlands
20	Hertfordshire	East Hertfordshire		Surrey	Waverley	Elmbridge
21	Lancashire	Wyre		Tyne and Wear	Sunderland	Newcastle upon Tyne	North Tyneside
22	Leicestershire	Hinckley and Bosworth		West Midlands	Birmingham
23	Leicestershire	Leicester		West Sussex	Horsham
24	Leicestershire	Blaby		West Yorkshire	Wakefield	Leeds
25	Lincolnshire	North East Lincolnshire		Wiltshire	Swindon
26	Nottinghamshire	Nottingham
27	Somerset	Bath and North East Somerset
28	South Yorkshire	Sheffield
29	Staffordshire	Stafford
30	Staffordshire	Staffordshire Moorlands
31	Surrey	Waverley
32	Surrey	Elmbridge
33	Tyne and Wear	Sunderland
34	Tyne and Wear	Newcastle upon Tyne
35	Tyne and Wear	North Tyneside
36	West Midlands	Birmingham
37	West Sussex	Horsham
38	West Yorkshire	Wakefield
39	West Yorkshire	Leeds
40	Wiltshire	Swindon
Main

Cell Formulas
Range	Formula
D2:D25	D2	=UNIQUE(FILTER(A2:A100,A2:A100<>""))
E2:E9,E13,E15:E18,E22:E23,E25,E24:F24,E21:G21,E19:F20,E14:G14,E11:F12,E10:K10	E2	=TRANSPOSE(FILTER($B$2:$B$100,$A$2:$A$100=D2))
Dynamic array formulas.

 

[Rahul1987]

Hello! I'm working on a project and need some help with a formula that can transpose data. In the example below, my data set is listed in two columns (Example 1). Ideally, I need it to be transposed so that the company information is provided in the first column, then the names for each company listed horizontally for each company (Example 2).

I can use the transpose function. However, with a larger dataset it can become pretty labor intensive. I've also attempted this using a combined XLOOKUP and OFFSET formula, but it requires a good bit of manual adjusting as well.

My hope is that someone can provide a formula or non-VBA option on this . Please review and let me know if you have any suggestions!

Example 1
View attachment 41163

Example 2
View attachment 41164

Answer - If someone does not understand ......then pls do not delete this post.

Book1.xlsx
	B	C	D	E	F	G	H	I	J	K	L
9			Unique	4
10					Columns						Helper1
11	Data		Sr	Rows	1	2	3	4	5		4
12	Company1	John	1	Company1	John	Alice	Tim				3
13	Company1	Alice	2	Company2	Rhodna						1
14	Company1	Tim	3	Company3	Alex	Brett	Rick	Denise			4
15	Company2	Rhodna	4	Company4	Rebekha	Rob					2
16	Company3	Alex
17	Company3	Brett
18	Company3	Rick
19	Company3	Denise
20	Company4	Rebekha
21	Company4	Rob
Sheet1

Cell Formulas
Range	Formula
E9	E9	=SUM(IF(FREQUENCY(IF($B$12:$B$21<>"",MATCH("~"&$B$12:$B$21,$B$12:$B$21&"",0)),ROW($B$12:$B$21)-ROW($B$12)+1),1))
G11:J11	G11	=F11+1
F12:J15	F12	=IF(COLUMNS($F11:F11)>$L12,"",INDEX($C$12:$C$21,SMALL(IF($B$12:$B$21=$E12,ROW($C$12:$C$21)-ROW($C$12)+1),COLUMNS($F11:F11))))
L11	L11	=MAX($L$12:$L$15)
L12:L15	L12	=COUNTIF($B$12:$B$21,E12)
D12:D16	D12	=IF(ROWS(D$12:D12)>$E$9,"",ROWS(D$12:D12))
E12:E21	E12	=IF(ROWS(E$12:E12)>$E$9,"",INDEX($B$12:$B$21,SMALL(IF(FREQUENCY(IF($B$12:$B$21<>"",MATCH("~"&$B$12:$B$21,$B$12:$B$21&"",0)),ROW($B$12:$B$21)-ROW($B$12)+1),ROW($B$12:$B$21)-ROW($B$12)+1),ROWS(E$12:E12))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Radoslaw Poprawski]

here is PQ solution

ex-285 - WYSZUKAJ.PIONOWO - kilka kryteriów - tablicowa z JEŻELI.xlsx
	A	B	C	D	E	F	G	H
1	company Name	CNCT name		company Name	Count.1	Count.2	Count.3	Count.4
2	C1	Andrew1		C1	Andrew1	Beatrice1	KillerMutantsuperBee1	whatNext?1
3	C2	Andrew2		C2	Andrew2	Beatrice2	KillerMutantsuperBee2	whatNext?2
4	C3	Andrew3		C3	Andrew3	Beatrice3	KillerMutantsuperBee3	whatNext?3
5	C4	Andrew4		C4	Andrew4	Beatrice4	KillerMutantsuperBee4	whatNext?4
6	C5	Andrew5		C5	Andrew5	Beatrice5	IHaveNoIdeaForNames1	whatNext?5
7	C6	Andrew1		C6	Andrew1	Beatrice6	IHaveNoIdeaForNames2	whatNext?6
8	C7	Andrew2		C7	Andrew2	Beatrice7	IHaveNoIdeaForNames3	whatNext?7
9	C8	Andrew3		C8	Andrew3	Beatrice8	IHaveNoIdeaForNames4	whatNext?8
10	C9	Andrew4		C9	Andrew4	Beatrice9	IHaveNoIdeaForNames5	whatNext?9
11	C10	Andrew5		C10	Andrew5	Beatrice10	IHaveNoIdeaForNames6	whatNext?10
12	C1	Beatrice1
13	C2	Beatrice2
14	C3	Beatrice3
15	C4	Beatrice4
16	C5	Beatrice5
17	C6	Beatrice6
18	C7	Beatrice7
19	C8	Beatrice8
20	C9	Beatrice9
21	C10	Beatrice10
22	C1	KillerMutantsuperBee1
23	C2	KillerMutantsuperBee2
24	C3	KillerMutantsuperBee3
25	C4	KillerMutantsuperBee4
26	C5	IHaveNoIdeaForNames1
27	C6	IHaveNoIdeaForNames2
28	C7	IHaveNoIdeaForNames3
29	C8	IHaveNoIdeaForNames4
30	C9	IHaveNoIdeaForNames5
31	C10	IHaveNoIdeaForNames6
32	C1	whatNext?1
33	C2	whatNext?2
34	C3	whatNext?3
35	C4	whatNext?4
36	C5	whatNext?5
37	C6	whatNext?6
38	C7	whatNext?7
39	C8	whatNext?8
40	C9	whatNext?9
41	C10	whatNext?10
Sheet2

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"company Name"}, {{"Count", each Text.Combine(List.Distinct([CNCT name]), ", "),  type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Count", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"Count.1", "Count.2", "Count.3", "Count.4"})
in
    #"Split Column by Delimiter"

 

[Excellency]

Thanks to all for your help! The TRANSPOSE/FILTER option worked perfectly!

 

[Fluff]

Glad we could help & thanks for the feedback.