A new Excel question

charlie clown · December 11, 2012

Basic scenario:

I have various columns of data

Items in the columns may appear in more than one column

Each item's position in each column is assigned a value

So let's say there are 3 columns:

Col.1

Car 10

Bike 20

Bus 30

Train 40

Plane 50

Col.2

Plane 10

Train 20

Taxi 30

Boat 40

Tram 50

Col.3

Train 10

Taxi 20

Boat 30

Bus 40

Car 50

I would like to do two things with this data:

- Find the total values in all columns for each item (eaasy enough to copy them all into a single worksheet and then sort by item type, and then sub-total for each item, I guess) - so in the above example if I ranked them by total values, Train, Boat and Bus would be at the top with 70, Plane and Car would have 60, Tram and Taxi both have 50 and so on down to Bike at the bottom with 10.

- Then add a weighting to each value depending on how many columns the items appeared in, so in the above example, I woudl like Train to be ranked higher than Boat and Bus because it appears in three different columns, whilst Boat and Bus, which has the same overall total, only appear in two columns. Similary, between Tram and Taxi, Taxi woudl be higher because it is in two columns, whilst Tram is in just one.

Suggestions (other than Java, hammer and helpfile related ones)?

TVM.

BoboS · December 11, 2012

This post is not viewable to guests.

You can sign in to your account at the login page here