Finding for Excel formula or VBA Code to solve problem

Brass Contributor

Finding for Excel formula or VBA Code to solve problem

I have an excel work book contains with two sheet as Sheet1 and Sheet2. in Sheet1 there is three columns named Column A - Chasis Number , Column B - License ID and Column C - Chasis Number. in column A is filled by some data which is also repeated in Column C. In Column B  Column B - License ID is related with Column C. in Sheet2 there is also three Column named Column A - Chasis Number , Column B - License ID and Column C - Chasis Number , but all column is empty. now i want to transfer the all repeated data of Column A and Column C  with related data in Column B of Sheet1 to Sheet2

as per column wise as it is with any Excel Formula or VBA Code.

Hope i can get Cooperation from tech community team in this regards

all of Yours Faithfully

TARUNKANTI!1964

6 Replies

Re: Finding for Excel formula or VBA Code to solve problem

You can achieve this task using VBA code. Below is a sample code that you can use to transfer the data from Sheet1 to Sheet2:

``````Sub TransferData()
Dim wsSource As Worksheet
Dim wsDestination As Worksheet
Dim lastRow As Long
Dim i As Long

' Set references to the source and destination worksheets
Set wsSource = ThisWorkbook.Sheets("Sheet1")
Set wsDestination = ThisWorkbook.Sheets("Sheet2")

' Find the last row with data in Sheet1
lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row

' Loop through each row in Sheet1
For i = 2 To lastRow ' Assuming data starts from row 2

' Check if the Chasis Number in Column A is equal to the Chasis Number in Column C
If wsSource.Cells(i, 1).Value = wsSource.Cells(i, 3).Value Then

' If they match, transfer the data to Sheet2
wsDestination.Cells(i, 1).Value = wsSource.Cells(i, 1).Value ' Copy Chasis Number
wsDestination.Cells(i, 2).Value = wsSource.Cells(i, 2).Value ' Copy License ID
wsDestination.Cells(i, 3).Value = wsSource.Cells(i, 3).Value ' Copy Chasis Number

End If

Next i

MsgBox "Data transfer complete!", vbInformation

End Sub``````

Here is how the code works:

1. It sets references to the source and destination worksheets (Sheet1 and Sheet2).
2. It finds the last row with data in Sheet1.
3. It loops through each row in Sheet1.
4. For each row, it checks if the Chasis Number in Column A matches the Chasis Number in Column C.
5. If they match, it transfers the data (Chasis Number, License ID, and Chasis Number) to the corresponding rows in Sheet2.

Make sure to adjust the sheet names and column references in the code to match your actual worksheet layout. The texts, steps and code were created with the help of AI.

My answers are voluntary and without guarantee!

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

Re: Finding for Excel formula or VBA Code to solve problem

Respected NikolinoDE, very much thank You for that You get my point and I have applied the Code Supplied by You but no DATA Transferred to Sheet2, may be I am wrong

With Humble Regards

@TARUNKANTI1964

Re: Finding for Excel formula or VBA Code to solve problem

Same as this post and also as this post

Re: Finding for Excel formula or VBA Code to solve problem

Respected Hans Vogelaar Sir,

actually what that is what I want You may find it which I shared in attached file as sample for Your understanding. ROW number may be extended up to 30000 in every Sheet.

EXCEL 2016

Re: Finding for Excel formula or VBA Code to solve problem

@TARUNKANTI1964
Respected Sir,

I saw the sample excel file and i have a formula which could help you to extract out the related data in column d and thereby you can paste the said data in the sheet 2 of yours and further i also noticed that there was a chasis number which repeated in the column A with difference License ID and chasis number for that you will get both the license id and chasis number of both the chasis number repeated in the column A.

the formula is =TEXTJOIN("/",TRUE,UNIQUE(FILTER(\$B\$2:\$C\$101,\$A\$2:\$A\$101=C2))) you can ditto the said formula to all the columns and thereby wherever columnA and Coumn C would be matched there would be details of license id from columnB and chasis number from Column C and then you can use =ISERROR(D2) this formula in column E and ditto the same and then to get only repeated data you can use this formula =FILTER(D2:D101,E2:E101=FALSE) in column F and by this you will get the repeated data and after copy pasting it you can delimit the data through "/" by using Text to column feature.

Re: Finding for Excel formula or VBA Code to solve problem

Respected Sir, i have not yet found any effective solution for my problem which i posted earlier

With Humble Regards
@TARUNKANTI1964