Need help with script or formula

Solved/Closed
josh07429 Posts 14 Registration date Friday June 23, 2017 Status Member Last seen November 20, 2017 - Updated on Jun 24, 2017 at 04:16 AM
 josh07429 - Jul 5, 2017 at 12:18 PM
Column A shows "Date" while column B shows "Passenger #"
I want to Copy and paste the Passenger # to Column C "Total Passenger"

The problem is I only need to paste the the values from Column B to C twice on the same day/date.

as you can see in my photo, I have 3 "12" on 5/3/2017, but I only needed to paste them two of them at C. I ignored the 3rd "12" and moved on to the next day which is 5/4/2017 to do the same thing.

This is a little complex so I don't really know how to do this than use a complex script using macro.

Thanks in advance.


2 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jun 26, 2017 at 11:19 AM
Hi Josh,

Give the following code a try:
Sub RunMe()
Dim x, y As Integer

y = 2

NewDate:
x = 0
If Range("B" & y) <> vbNullString Then
    Range("C" & y).Value = Range("B" & y).Value
    x = 1
End If

Do
    If Range("A" & y) = Range("A" & y - 1) And _
    Range("B" & y) <> vbNullString And _
    x < 2 Then
        Range("C" & y).Value = Range("B" & y).Value
        x = x + 1
    End If
    
    y = y + 1
    If Range("A" & y) <> Range("A" & y - 1) Then GoTo NewDate
    
Loop Until Range("A" & y) = vbNullString
End Sub


Hope you like it.

Best regards,
Trowa
1
THANK YOU!!

You're a life saver it worked :)

I only had a minor problem cause the dates have time but I just removed the time on the dates and your script works.

Thanks again.
0
josh07429 Posts 14 Registration date Friday June 23, 2017 Status Member Last seen November 20, 2017
Jun 27, 2017 at 02:29 PM
Hi, Is there a way to copy the highest value of the same day in column B to C? It seems that some of the numbers in B will sometimes have different numbers. I need to get the highest value and copy them to column C twice. Thanks in advance
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jul 4, 2017 at 11:52 AM
Hi Josh,

Quite the puzzle :)

See if the following code does the job:
Sub RunMe()
Dim x, y, HighVal, SecHighVal, Rep As Integer
Dim mDate As Date

x = 2
y = 2
NextDate:

Do
    mDate = Range("A" & x)
    y = y + 1
Loop Until mDate <> Range("A" & y)

Range(Cells(x, "B"), Cells(y - 1, "B")).Select

HighVal = Application.WorksheetFunction.Max(Selection)

SecHighVal = Application.WorksheetFunction.Large(Selection, 2)

For Each cell In Selection
    If cell.Value = HighVal And Rep < 2 Then
        cell.Offset(0, 1).Value = cell.Value
        Rep = Rep + 1
    End If
Next cell

For Each cell In Selection
    If cell.Value = SecHighVal And Rep < 2 Then
        cell.Offset(0, 1).Value = cell.Value
        Rep = Rep + 1
    End If
Next cell

Rep = 0

If Range("A" & y).Value = vbNullString Then Exit Sub

x = y
GoTo NextDate

End Sub


Let me know how the code performs!

Best regards,
Trowa



0
After changing some of the script it worked!!!

Thanks a lot :))))
0