Sorting numbers and doing a counta

  • Thread starter Thread starter tmi!tmi!
  • Start date Start date
T

tmi!tmi!

I have a spread sheet that has repeating numbers, i.e. techtip #'s, i
multiple columns.

I need to sort these numbers and do a counta on them. Example below:

Column A Column B
Row 1 1212 4545
Row 2 4545 1212
Row 3 1212 4545

Ideally, I need to have, at the bottom of each column, the sorte
techtip #'s and how many are there of each techtip # (counta).

Because of the number of techtip #'s, manually sorting and counting i
out of the question.

Thanks in advance,

Jef
 
Jeff,

There isn't a direct way to sort multiple columns as a single range of
values that I know of. Perhaps some one will come forward with one (likely
a macro). The normal thing is to have them in a single column. Then a
normal sort, and =COUNTA(A2:A100) would work, before and after a sort.
 
Perhaps one way ..

Taking your sample data in A1:B4 ..
(with *headers inserted* in row1)

Tech1 Tech2
1212 4545
4545 1212
1212 4545

and assuming empty cols to the right

1. Drive out uniques for Tech1 and Tech2 in cols D and E

Select A1:B4
Click Data > Filter > Advanced Filter
Check "Copy to another location" & "Unique records only"
Put in the "Copy to:" box : $D$1:$E$1
Click OK

(This'll extract the uniques into cols D and E)

2. Select and sort cols D and E *individually*

3. Now put in COUNTIFs in adjacent cols F and G
to count the occurences of each unique Tech#

Put in F2: =COUNTIF($A$2:$A$4,D2), fill down
Put in G2: =COUNTIF($B$2:$B$4,E2), fill down
 
Some clarifications ..
3. Now put in COUNTIFs in adjacent cols F and G
to count the occurences of each unique Tech#

Put in F2: =COUNTIF($A$2:$A$4,D2), fill down
Put in G2: =COUNTIF($B$2:$B$4,E2), fill down
Click to expand...

Col F will count the occurences of uniques in col D (Tech1)
Col G will count the occurences of uniques in col E (Tech2)

Adjust the ranges in the COUNTIFs to suit
 
Back
Top