Link to home
Create AccountLog in
Avatar of Wilder1626
Wilder1626Flag for Canada

asked on

VBA - Transfer Data from one sheet to another based on 3 columns

Hi again.

This is to follow previous post and adding to the complexity of single values, accept that it will be based on 3 columns.
https://www.experts-exchange.com/questions/29216053/VBA-Transfer-Data-from-one-sheet-to-another.html

Instead of transferring based on column "D" value only from "Template" sheet,  now it needs to be per columns "D" , "G" and "AB".

This is again without transferring duplicates values combinations.

Ex:
If in "Template" sheet i have below:
User generated image
We see that row 8 and 9 are identical on the 3 columns. So It would only transfer 1 single row. Not the duplicated row.

So in the sheet "Type", i would see below as a result:
User generated image

This is the code i have so far, but only transfers again based on column "D" only.
I just cannot see how to account for the 3 columns:
For Each V In dict and dict1 and dist2

Open in new window

Full code
Private Sub CommandButton1_Click()
  Dim dict As Object, rCell As Range, dict1 As Object, dict2 As Object
    
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare
    
    Set dict1 = CreateObject("Scripting.Dictionary")
    dict1.CompareMode = vbTextCompare
    
    Set dict2 = CreateObject("Scripting.Dictionary")
    dict2.CompareMode = vbTextCompare
        
    With Sheets("Template")
        For Each rCell In .Range("D7:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
            On Error Resume Next
            dict.Add rCell.Value, rCell.Offset(0, 1).Value
            dict1.Add rCell.Value, rCell.Offset(0, 3).Value
            dict2.Add rCell.Value, rCell.Offset(0, 24).Value
            
            On Error GoTo 0
        Next rCell
    End With

    With Sheets("Type")
        .UsedRange.Cells.Offset(1, 0).ClearContents

        For Each V In dict

            .Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = V
            .Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Value = dict1(V)
            .Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Value = dict1(V)
            .Cells(Rows.Count, 15).End(xlUp).Offset(1, 0).Value = dict2(V)
            
        Next V
    
    End With


End Sub

Open in new window


Thanks again for your help.

Macro Transfer single value to other sheet.xlsm
Avatar of Fabrice Lambert
Fabrice Lambert
Flag of France image

What's the goal behind this ?

This time I will suggest Power Query.:
Turn your "TEMPLATE" data into a data table.
Write a query, producing the desired results (remove blanks / keep unique rows / duplicate columns / shift columns / rename columns ect ...), insert it in the "Type" sheet.
On VBA side, it is just a matter of refreshing the workbook.

See the attached sample:
Macro-Transfer-single-value-to-othe.xlsm

Avatar of Wilder1626

ASKER

Hi Fabrice.

I will need to look at it more carefully about the Power Query option, cause i would still need a macro to add extra custom values in other columns, and not everyone that will use that excel file may understand that process.

But still interesting.
cause i would still need a macro to add extra custom values in other columns
Power Query can do that.

not everyone that will use that excel file may understand that process.
Well, Power Query is a relativelly unknown Excel feature (even tho, it has been around for almost 10 years).
It can do amazing thing without a single line of code, I strongly suggest that you (and your company) invest time into learning it.
As for your issue, if you want to stick with VBA, the algorythm is simple:
- Gather your data from the source sheet in a container.
- Eliminate dupes.
- Dispatch your data in the target sheet.
Thanks for the advice Fabrice. I will look at Power Query on my side for sure.

For this specific excel file macro, i will remain on VBA.
I'm trying to see how i can do:
- Gather your data from the source sheet in a container.
- Eliminate dupes.
- Dispatch your data in the target sheet.

Not that simple for me. I'm trying since last night.

ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
For this specific excel file macro, i will remain on VBA.
I'm trying to see how i can do:
- Gather your data from the source sheet in a container.
- Eliminate dupes.
- Dispatch your data in the target sheet.


Not that simple for me. I'm trying since last night.

Open in new window

Based on my post in your previous question, if a Pair class isn't enough, write a Triplet class, if not enough, write a Quadruplet class ect ....
If the amount of data in a single row is unknown, use an array, or a collection.

As for finding a key for each row that will be used for uniqueness test, concatenate every value from a single row into a string.

Thanks again to both of you.

Martin, your macro does it again. 
If you need any explanation about how it works, please let me know.