Excel Question: How many unique entries in more than one column?

[Rakido]

Hey Folks,

any Excel experts around? Not sure where to start with that problem:

Got a list of bands in different columns ("Alcest" actually starting in C9, the last current entry is somewhere in AQ 16). There are only 8 lines in each column, sometimes the last or last two lines in a column are empty

Alcest                             Amplifier                  Airbag              Airbag
Anathema                       Archive                    Alamaailman     Amplifier
Crippled Black Phoenix     Devin Townshend        Amplifier                    Beardfish
Flying Colors                  Elbow                    Daedalus                    Gazpacho
Gazpacho                          Haken                    Gran Torino                    Jane
Leprous                          Moonspell            Jolly                            Opeth
Pain of Salvation          Periphery            Panzerballet                    Overhead
Periphery                          Textures            ----------                    Renaissance 

What i want to have, is a list/chart/image/whatsever how often every band is named in those columns sorted by how often. So the output should be something like

(3 times) Amplifier
(2 times) Airbag
(2 times) Gazpacho
(2 times) Periphery
...
..
(1 times) Textures


Any idea how i can get that result. Could be a nice diagram as well ....

Thx a lot
R.

[Umfriend]

Now I have done more with Excel than most but these type of problems I can not solve, however simple they may seem conceptually. Basically, the lay-out of your data sucks. For this I would have to rely on VBA but would not look forward to it.

Is there a reason why you've put data in rows and columns?

[cjolley]

Email it to me and I will see what I can do. cwjolley at gmail

In case you want to try it, here is how I would go about it.
(Warning: I'm a database guy)

1, create a 1 column table in db of choice create table mytable(name varchar2(256)); (oracle in my case. there is a free version)
2, create new rows under each row of names in the spreadsheet.
3, create a function in the cell under the first name: ="insert into mytable values('" & c9 & "');"
4, paste that into all the cells under the names (you can do a row at a time)
5, copy the resulting rows of cells into a text editor (I use EditPad Lite)
6, replace tabs with new lines to give a long list of insert statements. This is the step that pivots your data.
7, paste the whole shooting match into a db interface (sqlplus in the case of oracle)
8, now you can run select count(*), name from mytable group by name order by 1 desc, 2;

Alternatively, you could copy the spreadsheet rows into the text editor and replace the tabs with newlines and just paste the result into a new or existing empty column.
Then use excel functions to do the report. But I don't know how to do that.

[VJ]

Clever solution!
And putting it all in a database makes more sense for other uses as well... For this purpose though, and since he has excel, maybe Access might be suitable enough (and not require new software to be installed).

[Umfriend]

Doh! Of course using a DBMS would do the trick as that addresses my complaint that the data structure is messy. If you have all the data in a single column in Excel a simple pivot table would do the trick.

Assuming you would want to find a more permanent solution you could first organise the data structure into one column. Next, assuming you want it in a table like you currently have create a seperate table using OFFSET to represent in a 8 rows by Y columns table reading from the list in the single column. Next, the pivot table would work oin the single column list as well. Both would perform AIW you change data.

For the table, assuming you have a one-column list in sheet "LIST", starting in row 11 and a sheet "TABLE" where you have values 1 to 8 in A2 to A9 and 1 to Y in row 1 (starting in B1) you could use in A2: =OFFSET(LIST!$A$10,$A2+8*(B$1-1),0,1,1). This formula you can copy to populate the entire table with. You may want to use something like =IF(formula=0,"",formula). You may need to add a column in TABLE once in a while as the list grows and LIST might need empty rows for columns where you want no text. Really depends on what the table is intended to represent.

But from data arranged as it is...

Ah, now that I think of it, my solution can be reversed, that is, you can creat a single column list for it and use pivottable on it.

Create a sheet "LIST" and in column A, increment 1 each cell (I typically like to start at row 11). In B11, use =OFFSET(TABLE!$C$8,MOD(A11-1,8)+1,ROUNDDOWN((A11-1)/8,0),1,1) and copy down in column B. This transforms the tabel to a single column list. On this, use pivot table.

[cjolley]

...
Create a sheet "LIST" and in column A, increment 1 each cell (I typically like to start at row 11). In B11, use =OFFSET(TABLE!$C$8,MOD(A11-1,8)+1,ROUNDDOWN((A11-1)/8,0),1,1) and copy down in column B. This transforms the tabel to a single column list. On this, use pivot table.

I figured there was probably a way to pivot the data in excel.
But all I had was a hammer, so I treated everything like a nail

[Rakido]

Now I have done more with Excel than most but these type of problems I can not solve, however simple they may seem conceptually. Basically, the lay-out of your data sucks. For this I would have to rely on VBA but would not look forward to it.

Is there a reason why you've put data in rows and columns?

I just get that excel from a friend of mine. I suggested to use a web based database from the beginning, but sometimes you just lose

The excel consists the (still ongoing) result of a raffle where people were asked to name bands that could possibly play at an OpenAir. Therefore the idea was to have the first column A fixed, later containing the bands that will play in reality and then compare those bands in column A manually with column B, just counting how many bands were guessed right. Same comparison with column C, then column D and so on, just scrolling to the right.

The reason for splitting the data into many columns is simple, the columns have more data than only the band names, email addresses and participant names and so on. So each column belongs to one person who participated.

The reason i'd like to have the total number of each band is to see if there's a band that a lot of people would like to see.

Umf, i'll try to work with your suggestions, but that has to wait til the weekend ...

Cj: thanks, as i said i suggested a database as well, but maybe i try that just out of fun

R.

[Umfriend]

And?