VBA code to change the format

[abdulncr]

Dear Friends,


I have data in excel sheet named LSR from A2:L3000 sample as below. i want to have a VBA code to change the data as below. please can i have help on this


Data before changing


FK PLATE NO Vehicle

23 1234DB toyota

23 98544DB ford

34 34322SW hyundai

21 34344TR nissan

451 43434FR toyota

39 43423TY nissan

39 34445FE ford

39 43455ER nissan

39 343444E landrover

189 44455E nissan


After running VBA code


FK PLATE NO Vehicle FK PLATE NO Vehicle FK PLATE NO Vehicle

23 1234DB toyota 23 98544DB ford

34 34322SW hyundai

21 34344TR nissan

451 43434FR toyota

39 43423TY nissan 39 34445FE ford 39 43455ER nissan 39 343444E landrover

189 44455E nissan


Thanks


Abdul

 

[SirJB7]

Hi, abdulncr!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


Give a look at this file:

https://dl.dropbox.com/u/60558749/VBA%20code%20to%20change%20the%20format%20%28for%20abdulncr%20at%20chandoo.org%29.xlsx


The yellowed area contains the new format you required for the data. Moving the range to another initial row or column (Ctrl-X, Insert Cut Cells) doesn't require adjusting the formulas of cells H2 in advance to calculate proper offset. Copying (Ctrl-C, Ctrl-V) maybe does.


Just advise if any issue.


Regards!

 

[Debraj]

@ SirJb7,

Hi Sir,

Just a Normal modification in Helper1 Column.. to fulfill the condition...

If same number occurred more than one and not in series.

I hope you wouldn't mind..


@ abdulncr,


I also prefer not to use VBA, if at all its possible by formula, just for the curiosity, VBA is also updated in the attached.


https://dl.dropbox.com/u/78831150/Excel/VBA%20code%20to%20change%20the%20format%20%28for%20abdulncr%20at%20chandoo.org%29.xlsm

[pre]

Sub SameInARow()
LC = Sheets(2).Range("a1").End(xlToRight).Column
LR = Sheets(2).Range("A65536").End(xlUp).Row
cnt = 2
Columns("E:XFD").Clear
With Application.WorksheetFunction
For I = 2 To LR
found = .CountIf(Range("A1:A" & I - 1), Range("A" & I))
If found = 0 Then
Range("A" & I & ":C" & I).Copy Range("E" & cnt)
cnt = cnt + 1
Else
Range("A" & I & ":C" & I).Copy Range("E" & _
.Match(Range("A" & I), Range("E2:E" & cnt), False) + 1).Offset(0, found * LC)
End If
Next I
End With
Range("a1:c1").Copy Range(Cells(1, 5), Cells(1, Sheets(2).UsedRange.Columns.Count))
End Sub

[/pre]

Please let us know if any issue.


Regards,

Deb

 

[abdulncr]

Thank you so much. it is working fine with me.


Thanks


Abdul

 

[SirJB7]

@Debraj Roy

Hi!

Absolutely don't mind. Even if the numbers were nor ordered (which I don't remember if checked or not) I assumed (wrongly I should say) that all were group together (as the example posted). But you're right.

Thanks for the sharpening update.

Regards!


Hi, abdulncr!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!