Merging Cell Values

[Sebastiens]

hi All

I have a gazzeteer list (a list of distances from area to area), please review the attached document.

I need to import these values into a database, so need to change the cells into the following format:

column1 column2 Distance
ADE ALB 1000
ADE ALT 500
ALB ADE 1000
ALB ALT 600
ALT ADE 500
ALT ALB 600
etc
I hope this makes sense?

I know I need a macro of sorts, but not sure how/where to start

Thanks

[jeffreybrown]

Hello & Welcome to the forum,

Take a look at sheet2...this can be done with a formula.

[Sebastiens]

Thanks Jeffrey
Thats pretty close, but I was hoping to auto match the vertical & horizontal ranges.
The reason is that there are 82 different zones, which would result in 6000+ matches.
Does this make sense?

[jeffreybrown]

The index/match does exactly that...

Matches ADE which is in B3 and matches ALB which is in E2 and returns 868.

Can you give a good example of what you want returned because honestly I didn't see any matched in your example and the spreadsheet was void of and outcomes?

[Sebastiens]

I can see that index/match works a treat. the issue I have is this:
I have a list of zones (82 of them). Each zone needs to be matched to each zone.
for example:
in column A I have ADE copied down 81 times
in column B I have each other zone
in column C i would then use the index/match that you've shown me to find the distance

the question I now have is, is there a way to automatically create the A & B columns?

i think I'm confusing you, but it's a little difficult to explain what it in writing

[jeffreybrown]

Yes maybe slightly, but you had the 100% right idea, provide a sample workbook but unfortunately all of the info was not provided up front.

Can you work up a small sample workbook with all of your requirements and some results so we can ensure the right answer is provided?

[davesexcel]

This may work for you, click the button to run the macro, when it is finished, check it out on Sheet2.
It also removes the rows with zeros and the blanks that occur from the formula.

[Sebastiens]

Dave, I'm not sure what you did, but PERFECT! thanks mate.