asking for help to solve my problem

[TARUNKANTI1964]

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 

Many thanks to all of tech community team member
@TARUNKANTI1964 

[#2]

@TARUNKANTI1964 

Do you mean repeated Chasis Number could be the same in any row of column A and/or in any row of column C, not the same numbers in the same row? If the former, for which rows to take License ID?

[#3]

Respected, actually I like to say repeat means if a Chesis Number found in Column C it may be found in Column A so in this situation I need which Chesis Number found in both Column A and C that would be transferred from Sheet1 to Sheet2 with Licence Number as per row would be found in Sheet1 in Column B

[#4]

Respected Sergei Baklan,due to my language problem i feel like i cannot clear my matter to You i apologies to You for this. actually in Sheet1 in Column A all Chesis Number must be Found in Column C but i do not know which Chasis Number in Column A are repeating in Column C in which Row. Now let's talk about License ID, actually License ID is related with Chasis Number found in Column C and it will must be transferred to Sheet2 in Column B. in Sheet2 there is also found Column A for Chasis Number, Column B for License ID and Column C for Chasis Number. now i want an Excel system by which i may get data in Sheet2 from Sheet1 as per Serially Row and Column wise. After that if You have any difficulty in understanding then You can let me know again
Many thanks to all of tech community team member

With Humble Regards

@TARUNKANTI1964

[#5]

@TARUNKANTI1964 

More clear, thank you. However, still not sure how it shall work. Perhaps you may share desired results on small sample, let say source data (Sheet1) is like

What shall be for above data in output (Sheet2)?

Another question is on which Excel your are, is that 365? If not, which version/platform?

[#6]

@SergeiBaklan 

Respected @Sergei Baklan Sir,

 Actually what that is what I want, You may find it which I shared in attached file as sample for Your understanding. Data shall be transferred as my per desire from Sheet 1 to Sheet 2 as per my attached Excel Book named “Sample in Short Form” According to Row wise. ROW number may be extended up to 30000 No's in every Sheet.

Platform - EXCEL 2016

Hope for Kind solicitation

With Humble Regards

@TARUNKANTI1964

[#7]

@TARUNKANTI1964 

For the sample file, let speak about Sheet2. 

In column A you enter data manually, i.e. 61 in A2, 69 in A3, etc., correct? If not, what is the logic behind, how the shall be selected.

In column C we just repeat the number which is in column A. Other words

=A2 in cell C2

=A2 in cell C3

etc.

Now, since we have no duplicates in column C of Sheet1, we could find License ID as

=INDEX(Sheet1!B:B, MATCH(A2,Sheet1!C:C,0) )

and drag it down.

If we could have duplicates in column C (as well as in column A) of Sheet1, that could be another approach. But with that it's not clear how to place result, see my previous question.

[#8]

@SergeiBaklan 

Respected Sergei Baklan Sir,

Yes, repeated Chasis Number such as Chasis No. 61 which would be found in Sheet 1, Column – A and Row – 12 and it also must be found in Column – C, otherwise it will not be otherwise, it will definitely be found, actually data in Column B and in Column C is basic data and data in Column B is co related (fixed) with data in Column C row wise as per my shared excel book “SAMPLE IN SHORT FORM”

Now I need all Chasis No which would be found in Column A, which are repeated with Chasis No also definitely found in Column C along with License No as per Row found in Column B that would be Transferred with Excel Formula or Excel VBA Code to Sheet 2 as shown in my shared file as marking with RED Highlighted which I had done it manually from Sheet 1

With Humble Regards

@TARUNKANTI1964

[#9]

@TARUNKANTI1964 

Back to the sample. #61 in column A is red highlighted. We have the same number in column C. However, #39 is not highlighted, even if we have it column C. What is the difference between #61 and #39, why first is highlighted and second not?

[#10]

Rrespected @SergeiBaklan Sir,

i want to let You know due to my language problem i don't quite understand my problem to You. for that reason i share again a Excel File where You will understand what i mean. Please see it in attached File.

Hope Your kind Cooperation will help me in this regard

With Humble Regards

@TARUNKANTI1964  

[#11]

@TARUNKANTI1964 

So you select chassis by marking font in red. Formulae don't work with properties as color, to recognize it we need to use VBA. And keep workbook as macro-enabled.

First, we may add VBA function like

Public Function RangeFontColor(ByRef myRange As Range)
    Dim i As Long
    Dim eachCell As Range
    
    ReDim myArray(myRange.Count - 1)

    For Each eachCell In myRange
        myArray(i) = eachCell.Font.ColorIndex
        i = i + 1
    Next
    RangeFontColor = myArray
    
End Function

Next, assuming you are on Excel 365 or 2021, use formula in Sheet2

=LET(
    colorIndex, 3,
    colA, Sheet1!$A2:$A10000,
    marked, FILTER( colA, TOCOL(RangeFontColor(colA) = colorIndex) ),
    licenses, XLOOKUP(marked, Sheet1!C:C, Sheet1!B:B, "not found"),
    chasis, XLOOKUP(marked, Sheet1!C:C, Sheet1!C:C, "not found"),
    HSTACK( marked, licenses, chasis)
)

If you are on another version of Excel that could be fully VBA solution. The latest is not my territory, perhaps @Hans_Vogelaar or someone else could help.

[#12]

@SergeiBaklan 

Respected @SergeiBaklan Sir,

I am using Excel 2016,

I was selecting Chasis Number by Red Colour Font that is what You said in Your reply dated on 29-04-2024, actually I did that for ease of understanding and I selected particular Chasis Number, License ID and Chasis Number as per in Column A, B, and Column C respectively in Sheet 1 which will transferred to Sheet 2 as per Column wise.  i describe in details at Sheet 2 in attached file

With Humble Regards

@TARUNKANTI1964

[#13]

@TARUNKANTI1964 

For the Excel 2016 that will be VBA solution, I can't help with it. You may wait if someone else suggests such solution; or start new thread, perhaps with reference on this one, mentioning directly you need solution for2016.

@Hans_Vogelaar ?

[#14]

@SergeiBaklan 

Respected @SergeiBaklan Sir

For Your understanding I have attached another sample like the one You showed me here. Please open the sample attachment I provided and You will understand what I really mean.

I am using Excel 2016

With Most Humble Regards

TARUN1964!

[#15]

Respected Mr. @Sergei Baklan Sir,

                                                        You are correct. As per Your Suggestion I requested to Respected Mr. Hans Vogelaar Sir, and HE Helped me a lot to solve my problem. I used formula provided by Respected Mr. Hans Vogelaar Sir as below in Sheet 2

=IF(Sheet1!A2="","",Sheet1!A2) , in Cell A2

=IFERROR(INDEX(Sheet1!$B$2:$B$30001, MATCH(B2, Sheet1!$C$2:$C$30000, 0)), ""),  in Cell B2.

=A2 in Cell C2

I am using Excel 2016

Many thanks to You for suggestion

With Humble Regards

@TARUNKANTI1964