Rearrange Excel List

andylowekcx

New Member
Joined
Feb 26, 2015
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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 NameColumn B - Company AddressColumn C - Contact NameColumn D - Contact Email
Andy Electrical1 High StreetBruce WayneBatman@gmail.com
Andy Electrical 1 High StreetPeter ParkerSpiderman@gmail.com
No Leaks Plumbing55 Water LaneCaptain CookHook@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 NameColumn B - Company AddressColumn C - Contact Name 1Column D - Contact Email 1Column E- Contact Name 2Column F - Contact Email 2
Andy Electrical1 High StreetBruce WayneBatman@gmail.comPeter ParkerSpiderman@gmail.com
No Leaks Plumbing55 Water LaneCaptain CookHook@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
 
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 NameColumn B - Company AddressColumn C - Contact NameColumn D - Contact Email
Andy Electrical1 High StreetBruce WayneBatman@gmail.com
Andy Electrical1 High StreetPeter ParkerSpiderman@gmail.com
No Leaks Plumbing55 Water LaneCaptain CookHook@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 NameColumn B - Company AddressColumn C - Contact Name 1Column D - Contact Email 1Column E- Contact Name 2Column F - Contact Email 2
Andy Electrical1 High StreetBruce WayneBatman@gmail.comPeter ParkerSpiderman@gmail.com
No Leaks Plumbing55 Water LaneCaptain CookHook@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.
 
Upvote 0
How about
Fluff.xlsm
ABCDEF
1Column A - Company NameColumn B - Company AddressColumn C - Contact NameColumn D - Contact Email
2Andy Electrical1 High StreetBruce WayneBatman@gmail.com
3Andy Electrical1 High StreetPeter ParkerSpiderman@gmail.com
4No Leaks Plumbing55 Water LaneCaptain CookHook@Office.com
5
6
7
8
9
10
11
12
13
14
15Andy Electrical1 High StreetBruce WayneBatman@gmail.comPeter ParkerSpiderman@gmail.com
16No Leaks Plumbing55 Water LaneCaptain CookHook@Office.com
17
Data
Cell Formulas
RangeFormula
A15:F16A15=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.
 
Upvote 0
Thanks for the reply.

Code:
=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.
 
Upvote 0
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.
 
Upvote 0
Hi Fluff

Ive managed to install the XL2BB.

Book1
ABCDEF
1Account.NameContact.FullNameContact.EmailContact.PhoneContact.Mobile
2Andy ElectricalDaron RubioDaron@gmail.com01234 56778907712 345678
3Andy ElectricalTanner StanleyTanner@gmail.com01235 56779007713 345679
4Andy ElectricalLester RamseyLester@gmail.com7874482162
5Andy ElectricalRoxie MarksRoxie@gmail.com07874482105
6Andy ElectricalElwood BullockElwood@gmail.com0113 5558324.47874E+11
7Andy ElectricalLandon HebertLandon@gmail.com
8Andy ElectricalJonathan RodriguezJonathan@gmail.com
9Andy ElectricalSeymour HaleySeymour@gmail.com1215554437874483351
10Andy ElectricalIan DanielIan@gmail.com
11Andy ElectricalWaylon SparksWaylon@gmail.com
12No Leaks PlumbingCaptain CookHook@Office.com01236 56779207714 345670
13
Sheet1


Hope this helps
 
Upvote 0
Thanks for that, how about
Excel Formula:
=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)),"|")))
 
Upvote 0
Another option:
Excel Formula:
=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)),""))
 
Upvote 0
Solution

Forum statistics

Threads
1,226,771
Messages
6,192,918
Members
453,766
Latest member
Gskier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top