Solved: Filter on matching data in two columns

[Dave T]

Hello All,

This probably quite simple but is giving me grief…

I have around 700 rows with route nodes that detail a specific section of road.

For example node 001 may be the South Australian border and node 02C may be Bordertown and this is used with a program as 00102C.
Correspondingly there is a reverse leg of that route shown as 02C001 from Bordertown (node 02C) to the SA border (node 001).
In another column alongside each route there is a text description of the route that goes from the start to the end. For example route 00102C will have a description from the state border to Bordertown and 02C001 will have the same description but reversed according to direction of travel.

Is there a formula or something that in another column would reverse only the second occurrence of the number so that in a third column there will be 00102C and another duplicate of 00102C (02C001 reversed). That would mean I can filter on a route number and there should only be two results returned, making it easier to check that the text details of the route for the second route is a copy of the first route but has been reversed correctly.

If it helps I have the first three digits of the start node of the route identifiers in column A and the last three digits of the node identifiers for the end of the route in column B. In column C I have concatenated columns A and B to make a six digit route leg.

If each 'pair' of routes can be uniquely identified then the AutoFilter in my example should return only 00102C and 02C001 (the forward and return legs of the route).


I hope all of this makes sense…

Regards,
Dave T

[sassora]

Hi Dave,

If they always come in pairs then you could has an additional column which says something like:

=IF(OR(A1=firstpart & secondpart, A1=secondpart & firstpart),1,0)
where A1 is a cell where to define the code you want to specify.

Then you would filter where this new column = 1.

[Dave T]

Hello sassora,

I appreciate your reply, but I only wish it was that simple…
Unfortunately the data as it comes out of the database is not in any particular order and the pairs can occur anywhere throughout the 700 or so rows.

In an effort to explain myself a bit clearer I have created a mock workbook with what I am trying to do.

Either option from columns F or G would suit my needs.
In column F I have just typed in the values, but a formula that could look at the data in column A and copy the first instance of a route and then when the reverse of it appears, copy that value i.e. first instance of 00102C (going from node 001 to 02C) is copied. If there is a reversal of that number (going from node 02C to 001) then the formula reverses the numbers and again returns a value of 00102C. That would mean when I filter on 00102C it would then return two only rows (forward direction and also the reverse).

In column G I just typed in the values but it is virtually the same as my previous example. Each pair would have the same unique number i.e. referring to my previous example each entry of would have a number 1 and the next row 02C03A would be number 2. If I filtered on an individual number it would again return only two rows.

Regards,
Dave T

[sassora]

Try this:

[Dave T]

Hello sassora,

I must admit that looking at your formula I thinking along the same lines, but I wasn’t that all that close
=IF(OR(A4=LEFT($A$1,3)&RIGHT($A$1,3),A4=RIGHT($A$1,3)&LEFT($A$1,3)),1,0)

I did actually use LEFT and RIGHT to extract the first and last three identifiers from the Link No., but was trying to use the two columns to achieve what your formula does from the original link number.

It works very well for finding the first instance of the reversed pair; however I did have another 300 or so pairs to find.

I ended up opting for the manual method and copied the first six character identifier and in the adjacent column a numerically increasing number and pasted these alongside the reversed link number.
It was a bit tedious but at least each time I use the AutoFilter it only returns two rows — the forward direction and the return direction for each link.

As I have just manually done what I was hoping for a formula to achieve, I will mark this as solved, but if there is numerically increasing solution I would still like to see it.

Thanks for your help, it is very much appreciated.

Regards,
Dave T

[snb]

or
=N(OR(B4&C4=$A$1;C4&B4=$A$1))



If you use advanced filter it can be accomplished simply :

make K1:L3 the criteria range
in K2 02C; in L2 001
in K3 001; in L3 02C

[sassora]

If you specifically wanted the same codes for each of the pair then this could be done with:
=IF(ISERROR(RIGHT(B2,1)*1),B2,RIGHT(B2,3)&LEFT(B2,3))

and copy down

It seemed that you wanted to be able to filter regardless of orientation, which is achieved with the formulas suggested.