Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching multiple columns
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching multiple columns
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. "Rothman" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching multiple columns
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? "JR" wrote: 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. "Rothman" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching multiple columns
....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) "Rothman" wrote: 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? "JR" wrote: 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. "Rothman" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching multiple columns
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 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Matching multiple columns
.....or....you could just create a pivot table and click "refresh" when you
add new numbers. "Herbert Seidenberg" wrote: 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 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Justify text across multiple columns | Excel Discussion (Misc queries) | |||
splitting 1 column of data into multiple columns | Setting up and Configuration of Excel | |||
drop down list multiple columns | Excel Discussion (Misc queries) | |||
Pivot Table combining multiple columns | Excel Discussion (Misc queries) | |||
HOW TO MATCH MULTIPLE COLUMNS WITH OR WITHOUT GAPS IN eXCEL ? | Excel Worksheet Functions |