merge columns with sorting

leecavturbo

Well-known Member
Joined
Jan 4, 2008
Messages
622
i have 2 columns of data ( label text ) the labels are location specific , the 1st & 3rd column details the location for each. how can my spreadsheet merge the 2 label columns so that the locations are in progressive rows?
so my data is F,G,H,I and i need J,K to auto sort
F is location, G is label, H is location, I is label. J should be location and K label sorted in order of location
TIA
 
Last edited:

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Panda514

New Member
Joined
Mar 11, 2019
Messages
37
Hello,

Can you give an example of what the first few rows of your data looks like and how you want it to be sorted? You said there are 2 columns of data, but then talked about the 1st and 3rd column. I am having trouble following your description.
 

leecavturbo

Well-known Member
Joined
Jan 4, 2008
Messages
622

ADVERTISEMENT

I can't run Macro's too due to admin rights
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,314
Office Version
  1. 2010
Platform
  1. Windows
ive manually filled J,K above but need auto fill if poss
Your thread title says "with sorting"... your output column appears to be sorted within each location individually but what determines the order of the locations as they are not sorted alphabetically?
 

leecavturbo

Well-known Member
Joined
Jan 4, 2008
Messages
622

ADVERTISEMENT

Hi Rick, i have manually populated the output column just to show what i should get. no order is required for location if that makes sense
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,314
Office Version
  1. 2010
Platform
  1. Windows
Hi Rick, i have manually populated the output column just to show what i should get. no order is required for location if that makes sense
I was going to give you a macro solution but I see you posted while I was writing my question to you that you cannot use a macro. I am afraid I do not know how to do what you want with formulas alone. Hopefully one of our formula experts will have a solution for you.
 

Panda514

New Member
Joined
Mar 11, 2019
Messages
37
ive manually filled J,K above but need auto fill if poss

I'm not sure if F and H have duplicates, but I would suggest copying all of column F and all of column H into 1 column on another sheet and then using the Remove Duplicates feature on the Data tab to get you a list of each Location. Then you can sort that A to Z if you want and past it into Column J.

For Column K, I have wrote a formula that will lookup the information in G and the lookup the information in I if nothing was found. So basically, for each location it checks in column G then in Column I and returns a blank if nothing is found.

=IFNA(IF(ISNA(VLOOKUP(J3,$F$3:$G$10,2,FALSE)),VLOOKUP(J3,$H$3:$I$10,2,FALSE),1),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,109,043
Messages
5,526,423
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top