Jump to content
By fans, for fans. By fans, for fans. By fans, for fans.

A new Excel question

charlie clown

Recommended Posts

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:



Car 10

Bike 20

Bus 30

Train 40

Plane 50



Plane 10

Train 20

Taxi 30

Boat 40

Tram 50



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)?



Link to comment
Share on other sites

Copy them all into a single list, create a pivot table and sort by category, using as your data values both a) the count of each entry (i.e. how many times 'boat' appears) and b) the sum of entry (i.e. the total associated with boat).


Sort the list however you like, presumably according to the count to get your 'weighting'. Would be close anough and take very little time/ effort?

Link to comment
Share on other sites

  • 1 month later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Create New...