Rearrange Excel List

[andylowekcx]

Hi Everyone

I have an excel list that i need to rearrange in preparation to be imported.

The list contains a list of companies, address, contacts & contact details. Currently a new row is made for each contact and the company name is duplicated.

example of current setup

Column A - Company Name	Column B - Company Address	Column C - Contact Name	Column D - Contact Email
Andy Electrical	1 High Street	Bruce Wayne	Batman@gmail.com
Andy Electrical	1 High Street	Peter Parker	Spiderman@gmail.com
No Leaks Plumbing	55 Water Lane	Captain Cook	Hook@Office.com

However for the import i need each company to only use 1 row and all the contacts to be on one column.

Column A - Company Name	Column B - Company Address	Column C - Contact Name 1	Column D - Contact Email 1	Column E- Contact Name 2	Column F - Contact Email 2
Andy Electrical	1 High Street	Bruce Wayne	Batman@gmail.com	Peter Parker	Spiderman@gmail.com
No Leaks Plumbing	55 Water Lane	Captain Cook	Hook@Office.com

Is it possible to reorganise my list like the second table, without doing it manually as I have almost 1000 contacts. Some company's have up to 10 contacts

Thanks in advance

Andy

 

[HighAndWilder]

Hi Everyone

I have an excel list that i need to rearrange in preparation to be imported.

The list contains a list of companies, address, contacts & contact details. Currently a new row is made for each contact and the company name is duplicated.

example of current setup

Column A - Company Name	Column B - Company Address	Column C - Contact Name	Column D - Contact Email
Andy Electrical	1 High Street	Bruce Wayne	Batman@gmail.com
Andy Electrical	1 High Street	Peter Parker	Spiderman@gmail.com
No Leaks Plumbing	55 Water Lane	Captain Cook	Hook@Office.com

However for the import i need each company to only use 1 row and all the contacts to be on one column.

Column A - Company Name	Column B - Company Address	Column C - Contact Name 1	Column D - Contact Email 1	Column E- Contact Name 2	Column F - Contact Email 2
Andy Electrical	1 High Street	Bruce Wayne	Batman@gmail.com	Peter Parker	Spiderman@gmail.com
No Leaks Plumbing	55 Water Lane	Captain Cook	Hook@Office.com

Is it possible to reorganise my list like the second table, without doing it manually as I have almost 1000 contacts. Some company's have up to 10 contacts

Thanks in advance

Andy

What version of Excel are you using? Please update your profile with this information.

This may determine what solution options there are.

 

[andylowekcx]

What version of Excel are you using? Please update your profile with this information.

This may determine what solution options there are.

Hi HighAndWilder

I have updated my profile, its 365.

Thanks

Andy

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F
1	Column A - Company Name	Column B - Company Address	Column C - Contact Name	Column D - Contact Email
2	Andy Electrical	1 High Street	Bruce Wayne	Batman@gmail.com
3	Andy Electrical	1 High Street	Peter Parker	Spiderman@gmail.com
4	No Leaks Plumbing	55 Water Lane	Captain Cook	Hook@Office.com
5
6
7
8
9
10
11
12
13
14
15	Andy Electrical	1 High Street	Bruce Wayne	Batman@gmail.com	Peter Parker	Spiderman@gmail.com
16	No Leaks Plumbing	55 Water Lane	Captain Cook	Hook@Office.com
17
Data

Cell Formulas
Range	Formula
A15:F16	A15	=LET(g,DROP(GROUPBY(A2:B10,C2:C10&"|"&D2:D10,HSTACK(LAMBDA(v,"|"&TEXTJOIN("|",,v)),COUNTA),,0,,A2:A10<>""),1),c,TAKE(g,,-1)*2,m,MAX(c),HSTACK(TAKE(g,,2),TEXTBEFORE(TEXTAFTER(INDEX(g,,3)&REPT("|",m-c+1),"|",SEQUENCE(,m)),"|")))
Dynamic array formulas.

 

[andylowekcx]

Thanks for the reply.

=LET(g,DROP(GROUPBY(A2:A924,B2:B924&"|"&C2:C924&"|"&D2:D924&"|"&E2:E924,HSTACK(LAMBDA(v,"|"&TEXTJOIN("|",,v)),COUNTA),,0,,A2:A924<>""),1),c,TAKE(g,,-1)*2,m,MAX(c),HSTACK(TAKE(g,,2),TEXTBEFORE(TEXTAFTER(INDEX(g,,3)&REPT("|",m-c+1),"|",SEQUENCE(,m)),"|")))

Ive decided not to use the address column, but each contact (column B) and email address (Column C) also has a phone & mobile number in columns D & E. I tried to edit to the formula above but all the contact details are in 1 column.

 

[Fluff]

In that case can you supply an updated example of your data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[andylowekcx]

Hi Fluff

Ive managed to install the XL2BB.

Book1
	A	B	C	D	E	F
1	Account.Name	Contact.FullName	Contact.Email	Contact.Phone	Contact.Mobile
2	Andy Electrical	Daron Rubio	Daron@gmail.com	01234 567789	07712 345678
3	Andy Electrical	Tanner Stanley	Tanner@gmail.com	01235 567790	07713 345679
4	Andy Electrical	Lester Ramsey	Lester@gmail.com		7874482162
5	Andy Electrical	Roxie Marks	Roxie@gmail.com		07874482105
6	Andy Electrical	Elwood Bullock	Elwood@gmail.com	0113 555832	4.47874E+11
7	Andy Electrical	Landon Hebert	Landon@gmail.com
8	Andy Electrical	Jonathan Rodriguez	Jonathan@gmail.com
9	Andy Electrical	Seymour Haley	Seymour@gmail.com	121555443	7874483351
10	Andy Electrical	Ian Daniel	Ian@gmail.com
11	Andy Electrical	Waylon Sparks	Waylon@gmail.com
12	No Leaks Plumbing	Captain Cook	Hook@Office.com	01236 567792	07714 345670
13
Sheet1

Hope this helps

 

[Fluff]

Thanks for that, how about

=LET(g,DROP(GROUPBY(A2:A14,B2:B14&"|"&C2:C14&"|"&D2:D14&"|"&E2:E14,HSTACK(LAMBDA(v,"|"&TEXTJOIN("|",,v)),COUNTA),,0,,A2:A14<>""),1),c,TAKE(g,,-1)*4,m,MAX(c),HSTACK(TAKE(g,,1),TEXTBEFORE(TEXTAFTER(INDEX(g,,2)&REPT("|",m-c+1),"|",SEQUENCE(,m)),"|")))

 

[Cubist]

Another option:

=LET(a,A2:A12,b,B2:E12,u,UNIQUE(a),IFNA(HSTACK(u,DROP(REDUCE("",u,LAMBDA(x,y,VSTACK(x,TOROW(FILTER(b,a=y))&""))),1)),""))

 

[andylowekcx]

That has worked. Thank you so much.