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

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