I have a problem with an excel spreadsheet that i need help with...
I am studying a road network and it is represented by points (nodes) and the
space inbetween these is the road.
My problem is that obviously a road goes from say point A to point B but
also back the way from point B to point A. The data i have contains both data
from point A to point B but also from point B to point A. This data is
identical and to i'd like to delete the repeated data from B to A. The
problem is that point A may go to up to 5 other locations, ie to B,C,D,E or -
as roads do at a junction. Therefore there is a fair amount of repeated data
that i can see no way of deleting easily.

Can anyone help?

Calum.

Brief sample of data....
Node A Node B Length Speed
150 151 0.2 50
150 176 0.6 60
150 198 0.3 40
150 162 0.2 50
150 193 0.4 50
150 249 0.2 60
151 150 0.2 50
151 152 1.1 60
152 151 1.1 60
152 153 0.2 60
153 152 0.2 60
153 154 0.3 60
153 164 0.88 70
154 153 0.3 60
162 150 0.2 50
162 163 0.2 50
163 162 0.2 50
163 164 0.4 50
164 153 0.88 70
164 163 0.4 50
164 165 0.2 50
165 164 0.2 50
165 166 0.6 50
166 165 0.6 50
166 167 0.2 50
167 166 0.2 50
167 168 0.3 50
168 167 0.3 50
168 169 0.1 50
169 168 0.1 50
169 192 0.2 40
176 150 0.6 60
177 178 0.7 50
178 177 0.7 50
178 179 0.3 50
179 178 0.3 50
179 180 0.1 30
179 189 0.4 45

You need to add a special identifier to each row that considers

150 160 €¦€¦€¦€¦
160 150 €¦€¦€¦€¦

the same. Try putting something like

=min(a2:b2) & max(a2:b2)

in cell e2 and copy all the way down. Make sure that there is a header
cell on the top of each column. Click the column e header cell and pull-down:

Data Filter Advanced Filter €¦

And check unique records only. Update your post if you still need help.

Good Luck

--
Gary's Student


"excel novice!" wrote:

I have a problem with an excel spreadsheet that i need help with...
I am studying a road network and it is represented by points (nodes) and the
space inbetween these is the road.
My problem is that obviously a road goes from say point A to point B but
also back the way from point B to point A. The data i have contains both data
from point A to point B but also from point B to point A. This data is
identical and to i'd like to delete the repeated data from B to A. The
problem is that point A may go to up to 5 other locations, ie to B,C,D,E or -
as roads do at a junction. Therefore there is a fair amount of repeated data
that i can see no way of deleting easily.

Can anyone help?

Calum.

Brief sample of data....
Node A Node B Length Speed
150 151 0.2 50
150 176 0.6 60
150 198 0.3 40
150 162 0.2 50
150 193 0.4 50
150 249 0.2 60
151 150 0.2 50
151 152 1.1 60
152 151 1.1 60
152 153 0.2 60
153 152 0.2 60
153 154 0.3 60
153 164 0.88 70
154 153 0.3 60
162 150 0.2 50
162 163 0.2 50
163 162 0.2 50
163 164 0.4 50
164 153 0.88 70
164 163 0.4 50
164 165 0.2 50
165 164 0.2 50
165 166 0.6 50
166 165 0.6 50
166 167 0.2 50
167 166 0.2 50
167 168 0.3 50
168 167 0.3 50
168 169 0.1 50
169 168 0.1 50
169 192 0.2 40
176 150 0.6 60
177 178 0.7 50
178 177 0.7 50
178 179 0.3 50
179 178 0.3 50
179 180 0.1 30
179 189 0.4 45

Thanks for the suggestion but gives me a list of numbers that each node goes
to but dont know what to do with this and filtering doesnt seem to do
anything!

"excel novice!" wrote:

I have a problem with an excel spreadsheet that i need help with...
I am studying a road network and it is represented by points (nodes) and the
space inbetween these is the road.
My problem is that obviously a road goes from say point A to point B but
also back the way from point B to point A. The data i have contains both data
from point A to point B but also from point B to point A. This data is
identical and to i'd like to delete the repeated data from B to A. The
problem is that point A may go to up to 5 other locations, ie to B,C,D,E or -
as roads do at a junction. Therefore there is a fair amount of repeated data
that i can see no way of deleting easily.

Can anyone help?

Calum.

Brief sample of data....
Node A Node B Length Speed
150 151 0.2 50
150 176 0.6 60
150 198 0.3 40
150 162 0.2 50
150 193 0.4 50
150 249 0.2 60
151 150 0.2 50
151 152 1.1 60
152 151 1.1 60
152 153 0.2 60
153 152 0.2 60
153 154 0.3 60
153 164 0.88 70
154 153 0.3 60
162 150 0.2 50
162 163 0.2 50
163 162 0.2 50
163 164 0.4 50
164 153 0.88 70
164 163 0.4 50
164 165 0.2 50
165 164 0.2 50
165 166 0.6 50
166 165 0.6 50
166 167 0.2 50
167 166 0.2 50
167 168 0.3 50
168 167 0.3 50
168 169 0.1 50
169 168 0.1 50
169 192 0.2 40
176 150 0.6 60
177 178 0.7 50
178 177 0.7 50
178 179 0.3 50
179 178 0.3 50
179 180 0.1 30
179 189 0.4 45

O.K. then€¦
My fault for not giving details:

Advanced filter may have problems filtering formulae. So if you put my
formula into column e, copy them and paste them back as value. The formulae
will then be replaced by either text or numbers.

Notice that your first record and seventh record have the same ID 150151
even though the info in cols a and b are switched

Once you have replaced the formulae with values run the advanced filter.
The filter requires you to enter two ranges. Assuming you used column e for
the ID, set both ranges to:

$E:$E

And check unique values only.

Hopefully you list should shorten. The first record should appear, but the
seventh record (the same route backwards) should be hidden.

--
Gary's Student


"excel novice!" wrote:

Thanks for the suggestion but gives me a list of numbers that each node goes
to but dont know what to do with this and filtering doesnt seem to do
anything!

"excel novice!" wrote:

I have a problem with an excel spreadsheet that i need help with...
I am studying a road network and it is represented by points (nodes) and the
space inbetween these is the road.
My problem is that obviously a road goes from say point A to point B but
also back the way from point B to point A. The data i have contains both data
from point A to point B but also from point B to point A. This data is
identical and to i'd like to delete the repeated data from B to A. The
problem is that point A may go to up to 5 other locations, ie to B,C,D,E or -
as roads do at a junction. Therefore there is a fair amount of repeated data
that i can see no way of deleting easily.

Can anyone help?

Calum.

Brief sample of data....
Node A Node B Length Speed
150 151 0.2 50
150 176 0.6 60
150 198 0.3 40
150 162 0.2 50
150 193 0.4 50
150 249 0.2 60
151 150 0.2 50
151 152 1.1 60
152 151 1.1 60
152 153 0.2 60
153 152 0.2 60
153 154 0.3 60
153 164 0.88 70
154 153 0.3 60
162 150 0.2 50
162 163 0.2 50
163 162 0.2 50
163 164 0.4 50
164 153 0.88 70
164 163 0.4 50
164 165 0.2 50
165 164 0.2 50
165 166 0.6 50
166 165 0.6 50
166 167 0.2 50
167 166 0.2 50
167 168 0.3 50
168 167 0.3 50
168 169 0.1 50
169 168 0.1 50
169 192 0.2 40
176 150 0.6 60
177 178 0.7 50
178 177 0.7 50
178 179 0.3 50
179 178 0.3 50
179 180 0.1 30
179 189 0.4 45