PDA

View Full Version : Need easy excel help

BigDannn
05-05-2014, 07:43 AM
ok guys so I have a giant data set I'm trying to sort through but for simplicity ill just give an easy example

say I have the following columns of data

1 1
2 2
3 4
6 1
1 7
5 1
2 2
1 3

how can I make a pivot table that counts how many 1s 2s 3s etc

so the final output would be -

Number Count
1 6
2 3
3 2
4 1
5 1
6 1
7 1

whenever I do it I just get a count of 1 for each number

I can do it if I'm able to move all the data into a single column, but what I'm working with has over 2.5million rows and only 850k fit in excel (FML)

naturaltony
05-05-2014, 07:54 AM
I think there a v function like find v=1

You can add a conditional like
If V=1 make v Red color

naturaltony
05-05-2014, 07:56 AM
You have to add in the sum formula for a total
Like =sum (v=1)

I feel like that's not right but you get the idea

naturaltony
05-06-2014, 09:51 AM
Did you figure it out???

jinda628
05-06-2014, 10:09 AM
Do you have to use a pivottable? Because a simple countif function will do that.

http://i86.photobucket.com/albums/k96/jinjean/Misc/Countif_zps3fccbeaf.jpg (http://s86.photobucket.com/user/jinjean/media/Misc/Countif_zps3fccbeaf.jpg.html)

naturaltony
05-06-2014, 10:15 AM
I love how op asks for help then disappears

animal777
05-06-2014, 10:21 AM
i would open the developer window and write a quick macro to do it.

dim wsheet1 as worksheet

set wsheet1 = thisworkbook.worksheets(1)

dim x, y, numTotal as integer

numTotal = 0
y = 1

for x = 1 to end of rows
if wsheet1.range("A" & x).value = y
numTotal = numTotal + 1
end if
next x

then output the results

y is the number you are looking for, numTotal is the number of times it appeared. You could make this run over and over again through every
row and column you had in the sheet and then put the results into it's own field before starting again at the next number.

i1983
05-06-2014, 10:32 AM
http://i.imgur.com/84cUgBX.jpg

jinda628
05-06-2014, 10:38 AM
i would open the developer window and write a quick macro to do it.

Lol brah, if the OP cant do a simple countif function, how do you expect him to write a short macro?

animal777
05-06-2014, 10:56 AM
Lol brah, if the OP cant do a simple countif function, how do you expect him to write a short macro?

lol, true