Pull info from table of distances

The first bit is this:

=INDEX($F$1:$N$1,MATCH(C2,F2:N2,0))

copied down.

When you say "list in another tab", exactly what do you mean? What do you want to see? Where do you want to see it?

If you want multiple city names in E2, etc, then:


1. Can I use VBA?

2. If not can I use a helper column?

3. If not what is the MAXIMUM number of cities <100 km in your real data thast would need to be concatenated into a single cell (more than about 6 and option 3 isn't really a runner.

But if you want Toronto in a2 in another sheet/place and the cities <100 km in the column(s) next to A2, that's totally do-able.

HI JMPro and welcome to the forum,

See the attached where the formula in Column B is a Control+Shift+Enter formula. I also converted your table to another format so you could simply use a distance drop down to filter like you desire.

Distances between cities.xlsx

Originally Posted by MarvinP

HI JMPro and welcome to the forum,

See the attached where the formula in Column B is a Control+Shift+Enter formula. I also converted your table to another format so you could simply use a distance drop down to filter like you desire.

Attachment 769691

Hi Marvin,

You may have the simplest & most efficient pivot table for what I was looking to get. How can I replicate that conversion (from colomn, to column & distance column) with my 500 x 500 file ?


Thanks all for the support !

JM

For a full formula-based solution:

=IFERROR(INDEX($1:$1,AGGREGATE(15,6,COLUMN($F$2:$N$2)/(($F2:$N2> 0)*($F2:$N2< 1000)),COLUMNS($G13:G13))),"")

see file.

Hi,

There is a tool in Power Query called UnPivot that I converted your data to mine. It took about 30 seconds to do the conversion and I think you have this tool in 2016 version of Excel.
Read about it at: https://support.microsoft.com/en-us/...5-f588221c7098
It isn't that hard if you learn just a tad of Power Query.

Before Power Query I had long VBA that did the same thing. I'm really glad PQ added it into their toolkit.