NEED HELP

Posted by Ren on January 07, 2002 11:21 AM

I have a spreadsheet with 12 columns. Column 2 has the dates, column 3 has the project number, column 9 has the amounts, and column 11 indicates what source the info comes from. There are three sources( SC, SP, CP) So there is a separate line for every different source. The problem is that the amounts are sometimes counted up to 3 times, giving an overstated total. I need to delete the duplicates rows and add two more columns to the sources so that columns 11,12, 13, read SC, SP, CP and add Xs in columns 11,12, 13 indicating what sources the info came from. How can I do this? Is there a macro that I can use? Thanks.

Posted by on January 07, 2002 11:38 AM

Questions:
Is everything listed 3 times, or are there multiple listings only when there is more than 1 source. If the later is true, what columns do you need to check to be assured that you are working with a duplicate? Is the column 9 amount duplicated from the different sources? (In other words, can you just take the first "amount" and forget the others for a listing, or do you have to find the amount among the multiple listings?)

Posted by Ren on January 07, 2002 12:17 PM

Not all everything is listed 3 times. Somethings are list once or twice. I need to check columns B,C, and I(Net Pay). But Column I is sometimes off a little by a few cents. I could take any figure that matchts two out of the three times.

I guess all i need is for the macro to look at columns B,C and I to confirm that they are the same ones. Then, delete the rows that have the letter SC or SB in column 11. Finally, put a X in the SC Column (12) and the SB Column(13) if there was a column that contained a SC or SB and was deleted. Thanks

Posted by Tom Dickinson on January 07, 2002 6:31 PM

Okay, try this:

Sub Konsoldate()
Dim Cnt, Answ, EndRw As Integer
EndRw = 500
For Cnt = 1 To EndRw
If UCase(Range("K" & Cnt)) = "CP" Then
Range("K" & Cnt) = "X"
Else
If UCase(Range("K" & Cnt)) = "SC" Then
Range("L" & Cnt) = "X"
Else
Range("M" & Cnt) = "X"
End If
Range("K" & Cnt) = Empty
End If
If UCase(Range("B" & Cnt)) = UCase(Range("B" & Cnt + 1)) And _
UCase(Range("C" & Cnt)) = UCase(Range("C" & Cnt + 1)) Then
Range("A" & Cnt, "M" & Cnt + 1).Interior.ColorIndex = 4
Answ = MsgBox("Press YES to use the first amount - " & Range("I" & Cnt) & Chr(13) & _
"Press NO to use the second amount - " & Range("I" & Cnt + 1) & Chr(13) & _
"Press CANCEL to not consolidate these lines", vbYesNoCancel, "DUPLICATION - USE FIRST AMOUNT?")
If Answ = vbNo Then
Range("I" & Cnt) = Range("I" & Cnt + 1)
End If
If Answ &LT;&GT; vbCancel Then
If UCase(Range("K" & Cnt + 1)) = "CP" Then
Range("K" & Cnt) = "X"
Else
If UCase(Range("K" & Cnt)) = "SC" Then
Range("L" & Cnt + 1) = "X"
Else
Range("M" & Cnt + 1) = "X"
End If
Range("A" & Cnt + 1).EntireRow.Delete
Cnt = Cnt - 1
End If
End If
Range("A" & Cnt, "M" & Cnt + 1).Interior.ColorIndex = xlNone
End If
If Range("B" & Cnt) = Empty And Range("B" & Cnt + 1) = Empty And _
Range("C" & Cnt) = Empty And Range("C" & Cnt + 1) = Empty Then
Cnt = EndRw
End If
Next
End Sub

On line 3 (EndRw = 500) change the 500 to the last row in your listing. The macro will insure that column K is blank unless the source is CP (in which case it will put an "X"). It will also put an "X" in column L for SC, and in column M for SB. (You may want to change the "X"s to letters. With only an "X", you have to keep referring to the top of the column. If you do change it, you need to do it in both places: near the start of the macro, and also near the end of the macro where it consolidates lines.)

When the macro finds 2 rows with similar columns B and C, it will highlight them, and ask which amount to use. Just follow the directions in the message box.

Hope this helps

Posted by Ren on January 08, 2002 6:52 AM

Thanks Tom, the macro does not highlight the amounts or anything. Sometimes there are two amounts for one date so i need to be able to have the option to skip those if the numbers are drastically different. when i hit cancel twice it cancels the macro. Can you still help me? Thanks again for your help.