If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need help with formula
Hi!
I need to create a formula that will count unique (duplicating/repeating)values. I have numeric data in one column and text data in another.I want to create a formula that will look at a value in column b that will correspond with a value in column a, then give a count on the duplicate/repeating value. I was going to use a sumproduct formula, but there are over 3700 unique values. Here is an example of my data. Looking at the data below column b at Jdoe, and count how many time 123 appeared next to Jdoe in column A, then return the result in C1. The formula I was going to use =sumproduct((A1:A12000=123) *(B1:B12000="jdoe")). This would have to be created over 3700 times to get the correct result. This needs to be created for all names in column B, which could correspond to several different values in column a. There has to be a quicker way. Column A Column B Column C A1:A12000 B1:B12000 123 Jdoe 2 456 Rsmith 789 Bmiller 987 Jdoe 654 Sjones 321 Bmiller 123 Jdoe 789 mbutler Thanks T |
#2
|
|||
|
|||
Here's what I'd do:
1. Ensure col. A and col. B have headers. 2. Select col. A and col. B 3. Go to Data Pivot Table and PivotChart Report. 4. Hit Next twice. 5. Click the Layout button. 6. Drag the number header to the ROW area, then drag the name header underneath it, then drag the number header again to the DATA area. 7. Hit OK then Finish. 8. Right-click on one of the total rows and select Hide. 9. Select columns A thru C. 10. Edit Copy and then Edit Copy Paste Special Value. 11. Select the range in col. A then begins with the first name (probably cell A5) and ends with the last name. 12. Press F5 Special Blanks OK. 13. Press "=" and click the first name. 14. Press ctrlenter. 15. Now use your SUMPRODUCT function and drag down. Here's what mine looked like: =SUMPRODUCT((A5=Sheet1!$B$2:$B$9)*(B5=Sheet1!$A$2: $A$9)) HTH Jason Atlanta, GA -----Original Message----- Hi! I need to create a formula that will count unique (duplicating/repeating)values. I have numeric data in one column and text data in another.I want to create a formula that will look at a value in column b that will correspond with a value in column a, then give a count on the duplicate/repeating value. I was going to use a sumproduct formula, but there are over 3700 unique values. Here is an example of my data. Looking at the data below column b at Jdoe, and count how many time 123 appeared next to Jdoe in column A, then return the result in C1. The formula I was going to use =sumproduct ((A1:A12000=123) *(B1:B12000="jdoe")). This would have to be created over 3700 times to get the correct result. This needs to be created for all names in column B, which could correspond to several different values in column a. There has to be a quicker way. Column A Column B Column C A1:A12000 B1:B12000 123 Jdoe 2 456 Rsmith 789 Bmiller 987 Jdoe 654 Sjones 321 Bmiller 123 Jdoe 789 mbutler Thanks T . |
#3
|
|||
|
|||
Hi
I would use a pivot table for this. See: http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm -- Regards Frank Kabel Frankfurt, Germany schrieb im Newsbeitrag ... Hi! I need to create a formula that will count unique (duplicating/repeating)values. I have numeric data in one column and text data in another.I want to create a formula that will look at a value in column b that will correspond with a value in column a, then give a count on the duplicate/repeating value. I was going to use a sumproduct formula, but there are over 3700 unique values. Here is an example of my data. Looking at the data below column b at Jdoe, and count how many time 123 appeared next to Jdoe in column A, then return the result in C1. The formula I was going to use =sumproduct((A1:A12000=123) *(B1:B12000="jdoe")). This would have to be created over 3700 times to get the correct result. This needs to be created for all names in column B, which could correspond to several different values in column a. There has to be a quicker way. Column A Column B Column C A1:A12000 B1:B12000 123 Jdoe 2 456 Rsmith 789 Bmiller 987 Jdoe 654 Sjones 321 Bmiller 123 Jdoe 789 mbutler Thanks T |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel Formula | yuki | Worksheet Functions | 7 | September 28th, 2004 02:40 AM |
Add to formula | Pat | Worksheet Functions | 16 | September 24th, 2004 03:38 PM |
Converting a SUMPRODUCT formula to COUNTA active... | BeSmart | Worksheet Functions | 8 | March 27th, 2004 03:36 PM |
Alternative formula to HLookup | Bernie Deitrick | Worksheet Functions | 0 | March 26th, 2004 12:53 PM |
&[tab] Usage Twist | Jody | Worksheet Functions | 4 | January 10th, 2004 06:21 PM |