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:
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:
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:
Thanks again for your help.
Macro Transfer single value to other sheet.xlsm
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:
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:
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
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
Thanks again for your help.
Macro Transfer single value to other sheet.xlsm
VBAMicrosoft ExcelMicrosoft Office
Last Comment
Martin Liss
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.
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 columnsPower 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.
- Gather your data from the source sheet in a container.
- Eliminate dupes.
- Dispatch your data in the target sheet.
ASKER
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.
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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.
ASKER
Thanks again to both of you.
Martin, your macro does it again.
Martin, your macro does it again.
If you need any explanation about how it works, please let me know.
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