Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reduce duplicates to 1 with a count of how many before
Hi Howard,
Am Sat, 22 Feb 2014 12:04:37 -0800 (PST) schrieb L. Howard: I would have never found that. Now it does the work it's supposed to do but ends with the pair of #N/A's and type mismatch error pop up. have another look in SkyDrive. I changed the data to your last example and for CB2 all rows(1) to rows(2) Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reduce duplicates to 1 with a count of how many before
On Saturday, February 22, 2014 12:12:48 PM UTC-8, Claus Busch wrote:
Hi Howard, Am Sat, 22 Feb 2014 12:04:37 -0800 (PST) schrieb L. Howard: I would have never found that. Now it does the work it's supposed to do but ends with the pair of #N/A's and type mismatch error pop up. have another look in SkyDrive. I changed the data to your last example and for CB2 all rows(1) to rows(2) Regards Claus B. -- Hi Claus, I do believe it is all anchored down and running fine. Sure do appreciate it. Howard |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reduce duplicates to 1 with a count of how many before
Hi Claus,
I have growing frustration over what appears to be a moving target of what the final out come is supposed to be, and of course I'm in over my head on the code. Raw data in column A and as before move the non-P item up and over 1 row and 1 column. So now data is in column A and column B (which is not shown below.) Count the number of identical P-xxxx's with nothing in column B next to them and the number of identical P-xxxx's with ABCxxxx in column B next to them. P-1234 ABC and P-1234 ABC would be 2. P-4567 DEF and P-4567 HIJ would be 1 for each. P-1357 XXX and P-1388 XXX would be 1 for each. Those sum go in column C. P-4352 P-3534 P-4568 ABCDE123 P-3333 P-5506 CDEFG234 P-4352 P-3534 P-4568 ABCDE124 P-7679 P-9852 P-9876 ABCDE 1 P-5678 FGRTTTRGF 1 P-7675 HFHFHFH 1 P-8901 ABCDE 1 P-8901 4 P-3456 1 P-6543 2 P-54463 1 The item in column B is a "serial number" so most likely all the P-xxxx with a serial number in column C will be 1. But if you have a couple or three P-9876 ABCDE's, for example, then that number should be in column C. Notice P-8901 ABCDE and P-8901 are counted separate. Once the count is completed then the duplicates can be removes to show as the column A, B, C example. The data above shows format only. None of the final outcome data is actually part of the raw data. It is just a before and after view so to speak. This is my last shot at this caper, I feel I've abused the privilege of the news group and your patience too much. Thanks. Howard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reduce duplicates to 1 with a count of how many before
Hi Howard,
Am Sat, 22 Feb 2014 18:05:25 -0800 (PST) schrieb L. Howard: I have growing frustration over what appears to be a moving target of what the final out come is supposed to be, and of course I'm in over my head on the code. Raw data in column A and as before move the non-P item up and over 1 row and 1 column. can you send me a workbook with the data and the expected result that I can see it? Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reduce duplicates to 1 with a count of how many before
Hi Howard,
Am Sat, 22 Feb 2014 18:05:25 -0800 (PST) schrieb L. Howard: I have growing frustration over what appears to be a moving target of what the final out come is supposed to be, and of course I'm in over my head on the code. Raw data in column A and as before move the non-P item up and over 1 row and 1 column. So now data is in column A and column B (which is not shown below.) I got it! Change the For Each rngC Loop to: ..Range("C2:C" & LRow2).Formula = "=SumProduct(--(Sheet1!" _ & "$A$1:$A$" & LRow1 & "=A2),--(Sheet1!$B$1:$B$" & LRow1 & "= _ B2))" Sub Test_CB2() Dim LRow1 As Long, LRow2 As Long Dim myArr As Variant With Sheets("Sheet1") LRow1 = .Cells(.Rows.Count, 1).End(xlUp).Row myArr = .Range("A1:B" & LRow1) End With With Sheets("Sheet2") .Range("A2").Resize(LRow1, 2) = myArr .Range("A2:B" & LRow1 + 1).RemoveDuplicates _ Columns:=Array(1, 2), Header:=xlNo LRow2 = .Cells(.Rows.Count, 1).End(xlUp).Row .Range("C2:C" & LRow2).Formula = "=SumProduct(--(Sheet1!" _ & "$A$1:$A$" & LRow1 & "=A2),--(Sheet1!$B$1:$B$" & LRow1 & "= B2))" End With End Sub Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reduce duplicates to 1 with a count of how many before
On Sunday, February 23, 2014 12:01:48 AM UTC-8, Claus Busch wrote:
Hi Howard, Am Sat, 22 Feb 2014 18:05:25 -0800 (PST) schrieb L. Howard: I have growing frustration over what appears to be a moving target of what the final out come is supposed to be, and of course I'm in over my head on the code. Raw data in column A and as before move the non-P item up and over 1 row and 1 column. So now data is in column A and column B (which is not shown below.) I got it! Change the For Each rngC Loop to: .Range("C2:C" & LRow2).Formula = "=SumProduct(--(Sheet1!" _ & "$A$1:$A$" & LRow1 & "=A2),--(Sheet1!$B$1:$B$" & LRow1 & "= _ B2))" Sub Test_CB2() Dim LRow1 As Long, LRow2 As Long Dim myArr As Variant With Sheets("Sheet1") LRow1 = .Cells(.Rows.Count, 1).End(xlUp).Row myArr = .Range("A1:B" & LRow1) End With With Sheets("Sheet2") .Range("A2").Resize(LRow1, 2) = myArr .Range("A2:B" & LRow1 + 1).RemoveDuplicates _ Columns:=Array(1, 2), Header:=xlNo LRow2 = .Cells(.Rows.Count, 1).End(xlUp).Row .Range("C2:C" & LRow2).Formula = "=SumProduct(--(Sheet1!" _ & "$A$1:$A$" & LRow1 & "=A2),--(Sheet1!$B$1:$B$" & LRow1 & "= B2))" End With End Sub Regards Claus B. -- Yes that really seems to be the ticket. All my testing says BINGO! Really appreciate you efforts. And of course all the code is noted with '/By Claus as I pass it on. Thanks again. Regards, Howard |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reduce duplicates to 1 with a count of how many before
Hi Howard,
Am Sun, 23 Feb 2014 01:19:18 -0800 (PST) schrieb L. Howard: All my testing says BINGO! if you have same values in A with and without values in B macro Test_CB doesn't give you the expected result because the value from A is only once in the array. With Test_CB2 you get the expected result but you use another sheet. You can also copy the data from A:B to C:D and run RemoveDuplicates. Then the code must be changed slightly. If you want the output in the same sheet like Sheet1 in SkyDrive workbook have another look in SkyDrive an go to Sheet4 and run Test_CB4 Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count duplicates | Excel Programming | |||
Count Duplicates | Excel Discussion (Misc queries) | |||
Count Employee Work Time - Don't Count Duplicates | Excel Worksheet Functions | |||
Reduce columns and rows count? | Excel Worksheet Functions | |||
count a group of numbers but do not count duplicates | Excel Worksheet Functions |