Identify reversed cells, where A1=A2, B1=B2, C1=D2 & C2=D1

LitoII · Feb 4, 2014

Hello guys!

This forum has poven to be very useful when I'm having real troubles with Excel, so I hope you guys will be able to give me a hand this one time once again! Thank you in advance

)

My issue consists of a database of routes A-B-C-D. But sometimes there is A-B-D-C (A and B stay the same, but C and D are reversed, which is actually the same route but in the return direction). I would like to be able to identify such cases as the data consists of 10k entries and it is pretty much unhuman to start identifying them one by one...

In detail:
I've got Origin, Destination and up to three Connecting Points. Each of the five points has four components (four columns per point) - code, name, country, region/continent. And then the database gives specific details for each route in the last three columns. All in all, columns A-T define the route and U, V and W give details about the route.

I want to be able to identify reversed Connecting Points when Origin and Destination stay the same. If CP1 and CP2 are the same as CP2 and CP1 of another entry, it could identify that entry by posting "1" or "RETURN" in column X of the same row.

Please, let me know, if you have understood my query, or if you'd like me to specify it further :3

I'm looking forward to any help or advice that could follow!

Thank you very much in advance

Warm regards,
LitoII

Momentman · Feb 4, 2014

Can you post sample data showing before and after scenarios. If you need to use fictitious data, please do, but I think we need to see the structure of the worksheet to really be of help

LitoII · Feb 4, 2014

Data

Actual Trip

Origin

Destination

ConnectPoint1

ConnectPoint2

ConnectPoint3

LF

Origin

Destination

ConnectPoint1

ConnectPoint2

ConnectPoint3

LF

A

B

C

X

90%

A

B

X

C

90%

A

B

C

Y

89%

A

B

Y

C

89%

A

B

C

Z

75%

A

B

Z

C

75%

A

B

D

X

67%

A

B

X

D

67%

A

B

D

Y

97%

A

B

Y

D

97%

A

B

D

Z

54%

A

B

Z

D

54%

A

B

E

X

67%

A

B

X

E

67%

A

B

E

Y

88%

A

B

Y

E

88%

A

B

E

Z

81%

A

B

Z

E

81%

A

B

F

X

91%

A

B

X

F

91%

A

B

F

Z

99%

A

B

Z

F

99%

A

B

G

Z

45%

A

B

Z

G

45%

A

B

G

Y

61%

A

B

Y

G

61%

A

B

X

C

80%

B

A

C

X

80%

A

B

Y

C

71%

B

A

C

Y

71%

A

B

Z

C

99%

B

A

C

Z

99%

A

B

X

D

60%

B

A

D

X

60%

A

B

Y

D

87%

B

A

D

Y

87%

A

B

Z

E

54%

B

A

E

Z

54%

A

B

X

E

53%

B

A

E

X

53%

A

B

Y

F

21%

B

A

F

Y

21%

A

B

Z

G

35%

B

A

G

Z

35%

A

B

X

G

88%

B

A

G

X

88%

LitoII · Feb 4, 2014

Take into concideration Origin and Destination are not only A and B, there are other ones too. My problem is in the red cells. I can identify them very easily by sorting the columns and simply seeing them, but there are too many of them.. No idea how to make the computer see the non-sense too ;/

I need a model that identifies that there has already been an entry with the same text in columns A and E (I've skipped many columns in the example above) and that the text in columns I and M is inverted
(first red line: there has already been a line with A and B and with X and C, but inverted (C - X))

Identify reversed cells, where A1=A2, B1=B2, C1=D2 & C2=D1

LitoII

New Member

Excel Facts

Momentman

Well-known Member

LitoII

New Member

LitoII

New Member

Similar threads

Forum statistics

Share this page

Identify reversed cells, where A1=A2, B1=B2, C1=D2 & C2=D1

LitoII

New Member

Excel Facts

Momentman

Well-known Member

LitoII

New Member

LitoII

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock