Menu
Ask a question Report

Need help with script or formula [Solved/Closed]

josh07429 16 Posts Friday June 23, 2017Registration date November 20, 2017 Last seen - Jun 23, 2017 at 12:28 PM - Latest reply:  josh07429
- Jul 5, 2017 at 12:18 PM
Excel Ask a question Report
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.


See more 

5 replies

Best answer
Report Respond to TrowaD
TrowaD 2369 Posts Sunday September 12, 2010Registration dateModeratorStatus June 21, 2018 Last seen - Jun 26, 2017 at 11:19 AM
1
Thank you
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

Thank you, TrowaD 1

Something to say? Add comment

CCM has helped 1827 users this month

Report
josh07429 - Jun 26, 2017 at 12:56 PM
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.
Report Respond to josh07429
josh07429 16 Posts Friday June 23, 2017Registration date November 20, 2017 Last seen - 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
Report Respond to TrowaD
TrowaD 2369 Posts Sunday September 12, 2010Registration dateModeratorStatus June 21, 2018 Last seen - Jul 4, 2017 at 11:52 AM
0
Thank you
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



Report Respond to josh07429
josh07429 - Jul 5, 2017 at 12:18 PM
After changing some of the script it worked!!!

Thanks a lot :))))
Respond to TrowaD
Ask a question