Copy date of a cell if a value is repeated

Z

zargon

New Member
Joined
May 4, 2017
Messages
10
Hi I hope you can help me.

I have a sheet that contains 3 columns, Label, Chip and Date, the label column can contain duplicate data if this occurs i need copy this date in the next row that contains the same label.

Example:
Label Chip Date
133 9871452 09/05/17
133 8971453
133 9875488
134 7899984 10/05/17
134 7899875
135 8798458 11/05/17
135 8798458


Result:

Label Chip Date
133 9871452 09/05/17
133 8971453 09/05/17
133 9875488 09/05/17
134 7899984 10/05/17
134 7899875 10/05/17
135 8798458 11/05/17
135 7895412 11/05/17
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Formula
D2 =IF(C2<>"",C2,D1)
copy to down.
Paste values columnD to columnC.

macro
Code: 
Sub test()
Dim LR As Long, i As Long, buf
LR = cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LR
    If cells(i, 3).Value <> "" Then
        buf = cells(i, 3).Value
    Else
        cells(i, 3).velue = buf
    End If
Next
End Sub
 
Upvote 0
Thank you so much Takae, very cool... i hope not to bother i tried to implement a solution with your macro but i am rookie...

Well the real format is like this:
[TABLE="width: 807"]
<tbody>[TR]
[TD]V
[/TD]
[TD] W
[/TD]
[TD]X
[/TD]
[TD]Y
[/TD]
[TD]Z
[/TD]
[TD]AA
[/TD]
[TD]AB
[/TD]
[TD]AC
[/TD]
[TD]AD
[/TD]
[TD]AE
[/TD]
[TD]AF
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD] ID
[/TD]
[TD]LABEL
[/TD]
[TD]DAT 1
[/TD]
[TD]DAT 2
[/TD]
[TD]DAT 3
[/TD]
[TD]DAT 4
[/TD]
[TD]DAT 5
[/TD]
[TD]DAT 6
[/TD]
[TD]DAT 7
[/TD]
[TD]DAT 8
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD="align: right"]1330102
[/TD]
[TD="align: right"]10171
[/TD]
[TD="align: right"]12:02
[/TD]
[TD="align: right"]12:08
[/TD]
[TD="align: right"]13:00
[/TD]
[TD="align: right"]14:24
[/TD]
[TD="align: right"]14:40
[/TD]
[TD="align: right"]15:30
[/TD]
[TD="align: right"]16:28
[/TD]
[TD="align: right"]04:20
[/TD]
[/TR]
[TR]
[TD="align: right"]5
[/TD]
[TD="align: right"]1329807
[/TD]
[TD="align: right"]9598
[/TD]
[TD="align: right"]11:57
[/TD]
[TD="align: right"]12:20
[/TD]
[TD="align: right"]13:05
[/TD]
[TD="align: right"]14:10
[/TD]
[TD="align: right"]14:22
[/TD]
[TD="align: right"]15:02
[/TD]
[TD="align: right"]15:35
[/TD]
[TD="align: right"]03:15
[/TD]
[/TR]
[TR]
[TD="align: right"]6
[/TD]
[TD="align: right"]1329807
[/TD]
[TD="align: right"]9598
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7
[/TD]
[TD="align: right"]1329807
[/TD]
[TD="align: right"]9598
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8
[/TD]
[TD="align: right"]1331864
[/TD]
[TD="align: right"]9608
[/TD]
[TD="align: right"]15:49
[/TD]
[TD="align: right"]16:02
[/TD]
[TD="align: right"]16:12
[/TD]
[TD="align: right"]17:13
[/TD]
[TD="align: right"]17:25
[/TD]
[TD="align: right"]18:56
[/TD]
[TD="align: right"]19:14
[/TD]
[TD="align: right"]03:12
[/TD]
[/TR]
[TR]
[TD="align: right"]9
[/TD]
[TD="align: right"]1329708
[/TD]
[TD="align: right"]9608
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10
[/TD]
[TD="align: right"]5001027
[/TD]
[TD="align: right"]98327
[/TD]
[TD="align: right"]13:30
[/TD]
[TD="align: right"]13:22
[/TD]
[TD="align: right"]13:32
[/TD]
[TD="align: right"]14:09
[/TD]
[TD="align: right"]14:19
[/TD]
[TD="align: right"]14:54
[/TD]
[TD="align: right"]15:13
[/TD]
[TD="align: right"]01:51
[/TD]
[/TR]
[TR]
[TD="align: right"]11
[/TD]
[TD="align: right"]1307566
[/TD]
[TD="align: right"]98327
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12
[/TD]
[TD="align: right"]1306925
[/TD]
[TD="align: right"]98327
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13
[/TD]
[TD="align: right"]1307574
[/TD]
[TD="align: right"]98327
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

and i need this:


[TABLE="width: 807"]
<tbody>[TR]
[TD]V
[/TD]
[TD] W
[/TD]
[TD]X
[/TD]
[TD]Y
[/TD]
[TD]Z
[/TD]
[TD]AA
[/TD]
[TD]AB
[/TD]
[TD]AC
[/TD]
[TD]AD
[/TD]
[TD]AE
[/TD]
[TD]AF
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD] ID
[/TD]
[TD]LABEL
[/TD]
[TD]DAT 1
[/TD]
[TD]DAT 2
[/TD]
[TD]DAT 3
[/TD]
[TD]DAT 4
[/TD]
[TD]DAT 5
[/TD]
[TD]DAT 6
[/TD]
[TD]DAT 7
[/TD]
[TD]DAT 8
[/TD]
[/TR]
[TR]
[TD="align: right"]4
[/TD]
[TD="align: right"]1330102
[/TD]
[TD="align: right"]10171
[/TD]
[TD="align: right"]12:02
[/TD]
[TD="align: right"]12:08
[/TD]
[TD="align: right"]13:00
[/TD]
[TD="align: right"]14:24
[/TD]
[TD="align: right"]14:40
[/TD]
[TD="align: right"]15:30
[/TD]
[TD="align: right"]16:28
[/TD]
[TD="align: right"]04:20
[/TD]
[/TR]
[TR]
[TD="align: right"]5
[/TD]
[TD="align: right"]1329807
[/TD]
[TD="align: right"]9598
[/TD]
[TD="align: right"]11:57
[/TD]
[TD="align: right"]12:20
[/TD]
[TD="align: right"]13:05
[/TD]
[TD="align: right"]14:10
[/TD]
[TD="align: right"]14:22
[/TD]
[TD="align: right"]15:02
[/TD]
[TD="align: right"]15:35
[/TD]
[TD="align: right"]03:15
[/TD]
[/TR]
[TR]
[TD="align: right"]6
[/TD]
[TD="align: right"]1329807
[/TD]
[TD="align: right"]9598
[/TD]
[TD]11:57
[/TD]
[TD]12:20
[/TD]
[TD]13:05
[/TD]
[TD]14:10
[/TD]
[TD]14:22
[/TD]
[TD]15:02
[/TD]
[TD]15:35
[/TD]
[TD]03:15
[/TD]
[/TR]
[TR]
[TD="align: right"]7
[/TD]
[TD="align: right"]1329807
[/TD]
[TD="align: right"]9598
[/TD]
[TD]11:57
[/TD]
[TD]12:20
[/TD]
[TD]13:05
[/TD]
[TD]14:10
[/TD]
[TD]14:22
[/TD]
[TD]15:02
[/TD]
[TD]15:35
[/TD]
[TD]03:15
[/TD]
[/TR]
[TR]
[TD="align: right"]8
[/TD]
[TD="align: right"]1331864
[/TD]
[TD="align: right"]9608
[/TD]
[TD="align: right"]15:49
[/TD]
[TD="align: right"]16:02
[/TD]
[TD="align: right"]16:12
[/TD]
[TD="align: right"]17:13
[/TD]
[TD="align: right"]17:25
[/TD]
[TD="align: right"]18:56
[/TD]
[TD="align: right"]19:14
[/TD]
[TD="align: right"]03:12
[/TD]
[/TR]
[TR]
[TD="align: right"]9
[/TD]
[TD="align: right"]1329708
[/TD]
[TD="align: right"]9608
[/TD]
[TD]15:49
[/TD]
[TD]16:02
[/TD]
[TD]16:12
[/TD]
[TD]17:13
[/TD]
[TD]17:25
[/TD]
[TD]18:56
[/TD]
[TD]19:14
[/TD]
[TD]03:12
[/TD]
[/TR]
[TR]
[TD="align: right"]10
[/TD]
[TD="align: right"]5001027
[/TD]
[TD="align: right"]98327
[/TD]
[TD="align: right"]13:30
[/TD]
[TD="align: right"]13:22
[/TD]
[TD="align: right"]13:32
[/TD]
[TD="align: right"]14:09
[/TD]
[TD="align: right"]14:19
[/TD]
[TD="align: right"]14:54
[/TD]
[TD="align: right"]15:13
[/TD]
[TD="align: right"]01:51
[/TD]
[/TR]
[TR]
[TD="align: right"]11
[/TD]
[TD="align: right"]1307566
[/TD]
[TD="align: right"]98327
[/TD]
[TD]13:30
[/TD]
[TD]13:22
[/TD]
[TD]13:32
[/TD]
[TD]14:09
[/TD]
[TD]14:19
[/TD]
[TD]14:54
[/TD]
[TD]15:13
[/TD]
[TD]01:51
[/TD]
[/TR]
[TR]
[TD="align: right"]12
[/TD]
[TD="align: right"]1306925
[/TD]
[TD="align: right"]98327
[/TD]
[TD]13:30
[/TD]
[TD]13:22
[/TD]
[TD]13:32
[/TD]
[TD]14:09
[/TD]
[TD]14:19
[/TD]
[TD]14:54
[/TD]
[TD]15:13
[/TD]
[TD]01:51
[/TD]
[/TR]
[TR]
[TD="align: right"]13
[/TD]
[TD="align: right"]1307574
[/TD]
[TD="align: right"]98327
[/TD]
[TD]13:30
[/TD]
[TD]13:22
[/TD]
[TD]13:32
[/TD]
[TD]14:09
[/TD]
[TD]14:19
[/TD]
[TD]14:54
[/TD]
[TD]15:13
[/TD]
[TD]01:51
[/TD]
[/TR]
</tbody>[/TABLE]


Hope you can help me.

P.S. Sorry for the bad format.
 
Last edited:
Upvote 0
With the next modify i can get result in the Y column, now i have a question...

Sub test()
Dim LR As Long, i As Long, buf
LR = Cells(Rows.Count, 2).End(xlUp).Row
For i = 3 To LR
If Cells(i, 25).Value <> "" Then
buf = Cells(i, 25).Value
Else
Cells(i, 25).Value = buf
End If
Next
End Sub

How i can copy the same data to the Z, AA, AB, AC, AD, AE columns?
 
Upvote 0
modified code as follows.
Hope this helps.

Code: 
Sub test()
Dim LR As Long, i As Long, buf
LR = cells(Rows.Count, 23).End(xlUp).Row
For i = 4 To LR
    If cells(i, 25).Value <> "" Then
        buf = Range(cells(i, 25), cells(i, 32))
    Else
        Range(cells(i, 25), cells(i, 32)) = buf
    End If
Next
 Range(Range("Y3"), cells(LR, 32)).NumberFormatLocal = "h:mm;@"
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Count entry by time
Replies
3
Views
253
admvlad
A
A
  • Question Question
SUMIFS
Replies
1
Views
315
Rijnsent
Rijnsent
L
  • Solved
Help with Count occurrence and ignore if already exist based on criteria.
Replies
2
Views
175
Lukma
L
S
  • Solved
Formula to split date and time and work out energy usage trend
Replies
6
Views
470
Sundance_Kid
S
A
  • Solved
Date format changing ...
Replies
4
Views
220
adambc
A

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top