Matching multiple columns

[Guest]

Something tells me this is going to be easier to resolve than describing the
problem.

I have a column of data and then another column showing the frequencies of
the numbers within that column:

C3:C85 looks like:
14
4
14
22
9
5
(and so forth; basically random with some duplicates)

EA3:EA85 looks like (these are frequencies of column C):
8
5
8
2
7
7
(and so forth)

What I'd like to do is have a separate sheet where I have a column
(Sheet2!A) of the range of numbers in column C (i.e. 1,2,3,4...55, the end of
the range), and then an adjacent column (Sheet2!B) that reports the
corresponding frequency. So:

Sheet2!A Sheet2!B
1 (whatever is in EA)
2 (whatever is in EA)
3 (whatever is in EA)
4 (whatever is in EA)
5 7
(and so forth)

Although there are duplicate numbers in column C, the frequencies, of
course, will always be the same for whatever number is duplicated (so, 5 in C
will always have a freq of 7 as reported in EA).

Any help would be greatly appreciated for the formula that should go into
Sheet2!B that matches Sheet2!A to C and then to EA and then reports whatever
is in EA. It has to be a formula, since the dataset is expanding; I need the
new sheet to update accordingly.

Thank you very, very much in advance.

--Rothman

 

[Guest]

Copy the numbers in column C and paste them on sheet 2 column A. Then, sort
and delete duplicates. In column B, type "=countif(sheet 1!$C$3:$C$85,A1)".
Copy this formula beside your unique values as necessary. This formula gives
you the frequency of the values on sheet 1, in column C with sheet 2, cell A1
as your criteria.

 

[Guest]

Thanks for the help; this should work (again, this forum leaves me feeling
stupid).

I do wonder, however, if my column EA was just another series of numbers
unrelated to column C. How would you match them up then?

 

[Guest]

....you shouldn't feel that way; you have to start somewhere, right?

What I gave you below eliminates the need for column EA on sheet 1 but, if
it was just another series of numbers, you could include it in the count as
follows...

=countif(sheet 1!$C$3:$C$85,A1)+countif(sheet 1!$EA$3:$EA$85,A1)

 

[Herbert Seidenberg]

Since you want the sheet to update
whenever the dataset expands,
you cannot use sort, copy and delete.
Dynamic named ranges will solve that,
but it ain't easy.
Assume your dataset consists of 50 rows and
has numbers from 10 to 30, partly shown here:

bin0 bin2 freq
22 10 4
26 11 2
20 12 2
26 13 1
23 14 3
.... ... ...
17 27 2
29 28 2
25 29 2
10 30 2
30 #N/A 0
10 #N/A #N/A
17 #N/A #N/A
.... ... ...

Select the 3 columns, including the headers
and enough extra rows to leave room for expansion and
Insert > Name > Create > Top Row
Also create these names:
bin1 Refers To: =INDEX(bin0,1):INDEX(bin0,COUNTA(bin0))
bin3 Refers To: =INDEX(bin2,1):INDEX(bin2,COUNT(bin2))
set1 Refers To: =ROW(INDEX($A:$A,MIN(bin1)):INDEX($A:$A,MAX(bin1)))
Fill the bin2 column with this array formula:
=set1
Fill the freq column with this array formula:
=FREQUENCY(bin1,bin3)
or this formula:
=COUNTIF(bin1,bin3)
When you add numbers to bin0,
the other columns will update and expand automatically.

 

[Guest]

.....or....you could just create a pivot table and click "refresh" when you
add new numbers.