software

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

Copy the numbers in column C and paste them on sheet 2 column A. Then, sort
and delete duplicates. In column B, type quot;=countif(sheet 1!$C$3:$C$85,A1)quot;.
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.

quot;Rothmanquot; wrote:

gt; Something tells me this is going to be easier to resolve than describing the
gt; problem.
gt;
gt; I have a column of data and then another column showing the frequencies of
gt; the numbers within that column:
gt;
gt; C3:C85 looks like:
gt; 14
gt; 4
gt; 14
gt; 22
gt; 9
gt; 5
gt; (and so forth; basically random with some duplicates)
gt;
gt; EA3:EA85 looks like (these are frequencies of column C):
gt; 8
gt; 5
gt; 8
gt; 2
gt; 7
gt; 7
gt; (and so forth)
gt;
gt; What I'd like to do is have a separate sheet where I have a column
gt; (Sheet2!A) of the range of numbers in column C (i.e. 1,2,3,4...55, the end of
gt; the range), and then an adjacent column (Sheet2!B) that reports the
gt; corresponding frequency. So:
gt;
gt; Sheet2!A Sheet2!B
gt; 1 (whatever is in EA)
gt; 2 (whatever is in EA)
gt; 3 (whatever is in EA)
gt; 4 (whatever is in EA)
gt; 5 7
gt; (and so forth)
gt;
gt; Although there are duplicate numbers in column C, the frequencies, of
gt; course, will always be the same for whatever number is duplicated (so, 5 in C
gt; will always have a freq of 7 as reported in EA).
gt;
gt; Any help would be greatly appreciated for the formula that should go into
gt; Sheet2!B that matches Sheet2!A to C and then to EA and then reports whatever
gt; is in EA. It has to be a formula, since the dataset is expanding; I need the
gt; new sheet to update accordingly.
gt;
gt; Thank you very, very much in advance.
gt;
gt; --Rothman

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?

quot;JRquot; wrote:

gt; Copy the numbers in column C and paste them on sheet 2 column A. Then, sort
gt; and delete duplicates. In column B, type quot;=countif(sheet 1!$C$3:$C$85,A1)quot;.
gt; Copy this formula beside your unique values as necessary. This formula gives
gt; you the frequency of the values on sheet 1, in column C with sheet 2, cell A1
gt; as your criteria.
gt;
gt; quot;Rothmanquot; wrote:
gt;
gt; gt; Something tells me this is going to be easier to resolve than describing the
gt; gt; problem.
gt; gt;
gt; gt; I have a column of data and then another column showing the frequencies of
gt; gt; the numbers within that column:
gt; gt;
gt; gt; C3:C85 looks like:
gt; gt; 14
gt; gt; 4
gt; gt; 14
gt; gt; 22
gt; gt; 9
gt; gt; 5
gt; gt; (and so forth; basically random with some duplicates)
gt; gt;
gt; gt; EA3:EA85 looks like (these are frequencies of column C):
gt; gt; 8
gt; gt; 5
gt; gt; 8
gt; gt; 2
gt; gt; 7
gt; gt; 7
gt; gt; (and so forth)
gt; gt;
gt; gt; What I'd like to do is have a separate sheet where I have a column
gt; gt; (Sheet2!A) of the range of numbers in column C (i.e. 1,2,3,4...55, the end of
gt; gt; the range), and then an adjacent column (Sheet2!B) that reports the
gt; gt; corresponding frequency. So:
gt; gt;
gt; gt; Sheet2!A Sheet2!B
gt; gt; 1 (whatever is in EA)
gt; gt; 2 (whatever is in EA)
gt; gt; 3 (whatever is in EA)
gt; gt; 4 (whatever is in EA)
gt; gt; 5 7
gt; gt; (and so forth)
gt; gt;
gt; gt; Although there are duplicate numbers in column C, the frequencies, of
gt; gt; course, will always be the same for whatever number is duplicated (so, 5 in C
gt; gt; will always have a freq of 7 as reported in EA).
gt; gt;
gt; gt; Any help would be greatly appreciated for the formula that should go into
gt; gt; Sheet2!B that matches Sheet2!A to C and then to EA and then reports whatever
gt; gt; is in EA. It has to be a formula, since the dataset is expanding; I need the
gt; gt; new sheet to update accordingly.
gt; gt;
gt; gt; Thank you very, very much in advance.
gt; gt;
gt; gt; --Rothman

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

quot;Rothmanquot; wrote:

gt; Thanks for the help; this should work (again, this forum leaves me feeling
gt; stupid).
gt;
gt; I do wonder, however, if my column EA was just another series of numbers
gt; unrelated to column C. How would you match them up then?
gt;
gt; quot;JRquot; wrote:
gt;
gt; gt; Copy the numbers in column C and paste them on sheet 2 column A. Then, sort
gt; gt; and delete duplicates. In column B, type quot;=countif(sheet 1!$C$3:$C$85,A1)quot;.
gt; gt; Copy this formula beside your unique values as necessary. This formula gives
gt; gt; you the frequency of the values on sheet 1, in column C with sheet 2, cell A1
gt; gt; as your criteria.
gt; gt;
gt; gt; quot;Rothmanquot; wrote:
gt; gt;
gt; gt; gt; Something tells me this is going to be easier to resolve than describing the
gt; gt; gt; problem.
gt; gt; gt;
gt; gt; gt; I have a column of data and then another column showing the frequencies of
gt; gt; gt; the numbers within that column:
gt; gt; gt;
gt; gt; gt; C3:C85 looks like:
gt; gt; gt; 14
gt; gt; gt; 4
gt; gt; gt; 14
gt; gt; gt; 22
gt; gt; gt; 9
gt; gt; gt; 5
gt; gt; gt; (and so forth; basically random with some duplicates)
gt; gt; gt;
gt; gt; gt; EA3:EA85 looks like (these are frequencies of column C):
gt; gt; gt; 8
gt; gt; gt; 5
gt; gt; gt; 8
gt; gt; gt; 2
gt; gt; gt; 7
gt; gt; gt; 7
gt; gt; gt; (and so forth)
gt; gt; gt;
gt; gt; gt; What I'd like to do is have a separate sheet where I have a column
gt; gt; gt; (Sheet2!A) of the range of numbers in column C (i.e. 1,2,3,4...55, the end of
gt; gt; gt; the range), and then an adjacent column (Sheet2!B) that reports the
gt; gt; gt; corresponding frequency. So:
gt; gt; gt;
gt; gt; gt; Sheet2!A Sheet2!B
gt; gt; gt; 1 (whatever is in EA)
gt; gt; gt; 2 (whatever is in EA)
gt; gt; gt; 3 (whatever is in EA)
gt; gt; gt; 4 (whatever is in EA)
gt; gt; gt; 5 7
gt; gt; gt; (and so forth)
gt; gt; gt;
gt; gt; gt; Although there are duplicate numbers in column C, the frequencies, of
gt; gt; gt; course, will always be the same for whatever number is duplicated (so, 5 in C
gt; gt; gt; will always have a freq of 7 as reported in EA).
gt; gt; gt;
gt; gt; gt; Any help would be greatly appreciated for the formula that should go into
gt; gt; gt; Sheet2!B that matches Sheet2!A to C and then to EA and then reports whatever
gt; gt; gt; is in EA. It has to be a formula, since the dataset is expanding; I need the
gt; gt; gt; new sheet to update accordingly.
gt; gt; gt;
gt; gt; gt; Thank you very, very much in advance.
gt; gt; gt;
gt; gt; gt; --Rothman

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 he

bin0bin2freq
22104
26112
20122
26131
23143
..........
17272
29282
25292
10302
30#N/A0
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 gt; Name gt; Create gt; 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......or....you could just create a pivot table and click quot;refreshquot; when you
add new numbers.

quot;Herbert Seidenbergquot; wrote:

gt; Since you want the sheet to update
gt; whenever the dataset expands,
gt; you cannot use sort, copy and delete.
gt; Dynamic named ranges will solve that,
gt; but it ain't easy.
gt; Assume your dataset consists of 50 rows and
gt; has numbers from 10 to 30, partly shown he
gt;
gt; bin0bin2freq
gt; 22104
gt; 26112
gt; 20122
gt; 26131
gt; 23143
gt; ..........
gt; 17272
gt; 29282
gt; 25292
gt; 10302
gt; 30#N/A0
gt; 10#N/A#N/A
gt; 17#N/A#N/A
gt; ..........
gt;
gt; Select the 3 columns, including the headers
gt; and enough extra rows to leave room for expansion and
gt; Insert gt; Name gt; Create gt; Top Row
gt; Also create these names:
gt; bin1 Refers To: =INDEX(bin0,1):INDEX(bin0,COUNTA(bin0))
gt; bin3 Refers To: =INDEX(bin2,1):INDEX(bin2,COUNT(bin2))
gt; set1 Refers To: =ROW(INDEX($A:$A,MIN(bin1)):INDEX($A:$A,MAX(bin1)) )
gt; Fill the bin2 column with this array formula:
gt; =set1
gt; Fill the freq column with this array formula:
gt; =FREQUENCY(bin1,bin3)
gt; or this formula:
gt; =COUNTIF(bin1,bin3)
gt; When you add numbers to bin0,
gt; the other columns will update and expand automatically.
gt;
gt;