Mapping one sheet to another?

A

AndyGray

New Member
Joined
May 31, 2017
Messages
30
Hi Guys, i'm trying to find a pretty long winded formula which hopefully will save me a few days work...

I will simplify everything as much as I can then show an example...

I have the following fields.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Type
[/TD]
[TD]Sku
[/TD]
[TD]Var1Name
[/TD]
[TD]Var1Value
[/TD]
[TD]Var2name
[/TD]
[TD]Var2Value
[/TD]
[TD]Var3name
[/TD]
[TD]Var3Value
[/TD]
[/TR]
[TR]
[TD]Variable
[/TD]
[TD]Tshirt1
[/TD]
[TD]Colour
[/TD]
[TD]White,Black
[/TD]
[TD]Size
[/TD]
[TD]Medium,Large
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]variation
[/TD]
[TD]TshirtWhiteM
[/TD]
[TD]Colour
[/TD]
[TD]White
[/TD]
[TD]Size[/TD]
[TD]Medium
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]variation
[/TD]
[TD]TshirtWhiteL
[/TD]
[TD]Colour
[/TD]
[TD]White
[/TD]
[TD]Size
[/TD]
[TD]Large
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]variation
[/TD]
[TD]TshirtBlackM
[/TD]
[TD]Colour
[/TD]
[TD]Black
[/TD]
[TD]Size
[/TD]
[TD]Medium
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]variation
[/TD]
[TD]TshirtBlackL
[/TD]
[TD]Colour
[/TD]
[TD]Black
[/TD]
[TD]Size
[/TD]
[TD]Large
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Variable
[/TD]
[TD]Jumper
[/TD]
[TD]Size
[/TD]
[TD]Small, Medium, Large
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]variation
[/TD]
[TD]JumperS
[/TD]
[TD]Size
[/TD]
[TD]Small
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]variation
[/TD]
[TD]JumperM
[/TD]
[TD]Size
[/TD]
[TD]Medium
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]variation
[/TD]
[TD]JumperL
[/TD]
[TD]Size
[/TD]
[TD]Large
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The bold fields are the data which i am missing on the spreadsheet. This is heald in another sheet, which is set up in a different format. Where the above table only shows variables for the products in question. This sheet i am working off has them layed out such as... (V1N = Variable 1 name, V1v = Variable 1 value)


V1N, V1V, V2N, V2V, V3N, V3V, V4N,V4V......... V15N, V15V...Variable name always contains a value, So V1 Name will say "Length" all the way down, V2 would be height all the way down.. When required to add a variable it is added next to the appropriate "value" collumn.

I am needing to do the following...
I need Sheet 1 to look at value "Sku" as a reference. Then if VAR1Name contains "colour".. Then look in sheet 2 for the same sku and find colour in there (say it found colour in cell P2. I want it then to select P3, and copy it into book 1 (under the collumn Var1value)
Then want it to procede to the next collumn (which would be var2Name) and do the same, to bring back a result for "Size" from sheet 2)

The only possible way i can think of this is with a vlookup and an if statement. the if statement assigns a number to every value.. then the vlookup looks for that value in the other sheet? but not show how it would work of it theres a less long winded way??
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Hi Andy,

Assuming that based on your description Sheet2 is laid out like this :

[TABLE="width: 808"]
<tbody>[TR]
[TD]Sku[/TD]
[TD]Colour[/TD]
[TD]ColourValue[/TD]
[TD]Size[/TD]
[TD]SizeValue[/TD]
[TD]Length[/TD]
[TD]LengthValue[/TD]
[TD]Material[/TD]
[TD]MaterialValue[/TD]
[/TR]
[TR]
[TD]Tshirt1[/TD]
[TD]Colour[/TD]
[TD]White,Black[/TD]
[TD]Size[/TD]
[TD]Medium,Large[/TD]
[TD]Length[/TD]
[TD]45[/TD]
[TD]material[/TD]
[TD]Cotton[/TD]
[/TR]
[TR]
[TD]Jumper[/TD]
[TD]Colour[/TD]
[TD]White[/TD]
[TD]Size[/TD]
[TD]Small,Medium,Large[/TD]
[TD]Length[/TD]
[TD]55[/TD]
[TD]material[/TD]
[TD]Wool[/TD]
[/TR]
</tbody>[/TABLE]
..then the following formula would work in your inserted Var1Value , Var2Value columns etc in Sheet1.

Code: 
=INDEX(INDIRECT("Sheet2!$" & MATCH($B2,Sheet2!$A:$A,0) & ":$" & MATCH($B2,Sheet2!$A:$A,0)),1,MATCH(C2,INDIRECT("Sheet2!$" & MATCH($B2,Sheet2!$A:$A,0) & ":$" & MATCH($B2,Sheet2!$A:$A,0)),0)+1)

Personally I always find it easier to build long formulas like this with a helper column(s) (which you then hide) so that the final formula (COLUMN F) is easier to read, eg :


[TABLE="width: 1034"]
<tbody>[TR]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Sku[/TD]
[TD]MatchingRow[/TD]
[TD]Location of Variable Data[/TD]
[TD]Var1Name[/TD]
[TD]Var1Value[/TD]
[/TR]
[TR]
[TD]Jumper[/TD]
[TD]=MATCH($B3,Sheet2!$A:$A,0)[/TD]
[TD]="Sheet2!$"&C3&":$"&C3[/TD]
[TD]Size[/TD]
[TD]=INDEX(INDIRECT($D3),1,MATCH(E3,INDIRECT($D3),0)+1)[/TD]
[/TR]
[TR]
[TD]Tshirt1[/TD]
[TD]=MATCH($B4,Sheet2!$A:$A,0)[/TD]
[TD]="Sheet2!$"&C4&":$"&C4[/TD]
[TD]Size[/TD]
[TD]=INDEX(INDIRECT($D4),1,MATCH(E4,INDIRECT($D4),0)+1)[/TD]
[/TR]
[TR]
[TD]Jumper2[/TD]
[TD]=MATCH($B5,Sheet2!$A:$A,0)[/TD]
[TD]="Sheet2!$"&C5&":$"&C5[/TD]
[TD]Size[/TD]
[TD]=INDEX(INDIRECT($D5),1,MATCH(E5,INDIRECT($D5),0)+1)[/TD]
[/TR]
[TR]
[TD]JumperL[/TD]
[TD]=MATCH($B6,Sheet2!$A:$A,0)[/TD]
[TD]="Sheet2!$"&C6&":$"&C6[/TD]
[TD]Size[/TD]
[TD]=INDEX(INDIRECT($D6),1,MATCH(E6,INDIRECT($D6),0)+1)[/TD]
[/TR]
</tbody>[/TABLE]

Before hiding the helper columns (C & D) will look something like this :

[TABLE="width: 687"]
<tbody>[TR]
[TD]Sku[/TD]
[TD]MatchingRow[/TD]
[TD]Location of Variable Data[/TD]
[TD]Var1Name[/TD]
[TD]Var1Value[/TD]
[TD]Var2name[/TD]
[TD]Var2Value[/TD]
[/TR]
[TR]
[TD]Jumper[/TD]
[TD="align: center"]3[/TD]
[TD]Sheet2!$3:$3[/TD]
[TD]Size[/TD]
[TD]Small,Medium,Large[/TD]
[TD]Colour[/TD]
[TD]White[/TD]
[/TR]
[TR]
[TD]Tshirt1[/TD]
[TD="align: center"]2[/TD]
[TD]Sheet2!$2:$2[/TD]
[TD]Size[/TD]
[TD]Medium,Large[/TD]
[TD]Colour[/TD]
[TD]White,Black[/TD]
[/TR]
[TR]
[TD]Jumper2[/TD]
[TD="align: center"]6[/TD]
[TD]Sheet2!$6:$6[/TD]
[TD]Size[/TD]
[TD]Large[/TD]
[TD]Length[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]JumperL[/TD]
[TD="align: center"]9[/TD]
[TD]Sheet2!$9:$9[/TD]
[TD]Size[/TD]
[TD]Large[/TD]
[TD]Length[/TD]
[TD]65[/TD]
[/TR]
</tbody>[/TABLE]

The formula in Column C (matching Row) assumes that the Data in Sheet 2 has the matching Item Names (SKUs) in Column A - you would simply adjust that if there are other columns to the left of the SKUs on Sheet2.

Hope that helps !
Cheers,
Warren K.
 
Upvote 0
Thanks for the reply Warren, I've looked into this a little bit more and took your advice with helper collumns, but what i've done is in sheet 2 counted the collumns, assigned these a number to make a vlookup table, then done one vlookup saying "Vlookup SKU (in reference table) to bring back a number for the variable 1 name.

Then done a vlookup which will use the assigned number to choose which field to display in a vlookup table. took about 4 steps but I've managed to get it somewhere near.. The indirect function is something i've not learnt to fully understand yet, i've used it once with help from someone on here and it's really a powerful tool.

Another addition to this is something else i am trying to achieve...

I'm wanting to concatenate some rows dependent upon if they are a different value.

So in column C i want to say =IF(D2="Variable",concatenate(c3,c4),c2)

So i need the formula to check if parent sku is the same as the child sku, then if it does, then concatenate both of them skus together, and display in the cell C2?

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]SKU[/TD]
[TD]Parent Sku[/TD]
[TD]Variable value[/TD]
[TD]Product Type[/TD]
[/TR]
[TR]
[TD]Parent1[/TD]
[TD][/TD]
[TD]Blonde, Brown[/TD]
[TD]Variable[/TD]
[/TR]
[TR]
[TD]Child1[/TD]
[TD]Parent1[/TD]
[TD]Blonde[/TD]
[TD]variation[/TD]
[/TR]
[TR]
[TD]Child2[/TD]
[TD]Parent1[/TD]
[TD]Brown[/TD]
[TD]variation[/TD]
[/TR]
[TR]
[TD]Daddy1[/TD]
[TD][/TD]
[TD]Blonde, Brown, Ginger[/TD]
[TD]variable[/TD]
[/TR]
[TR]
[TD]Baby1[/TD]
[TD]Daddy1[/TD]
[TD]Blonde[/TD]
[TD]variation[/TD]
[/TR]
[TR]
[TD]Baby2[/TD]
[TD]Daddy1[/TD]
[TD]Brown[/TD]
[TD]variation[/TD]
[/TR]
[TR]
[TD]Baby3[/TD]
[TD]Daddy1[/TD]
[TD]Ginger[/TD]
[TD]variation
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Hi Andy,

Could another way to describe the logic for this extra part be "Concatenate the entries in all the Child Cells between here and the next "variable" (Parent) Row ?

By the way, what you currently have proposed would be a circular reference on the the child entries.

So in column C i want to say =IF(D2="Variable",concatenate(c3,c4),c2)
Click to expand...

IE either you are only putting this formula onto the Parent rows (bold in the example data) or else you will get a circular reference on the child rows. (The IF(false) entry of C2 going into Cell C2 is not possible...)

(or you could have another column that SHOWS this data (either the same as the original data or the concatenated data for "parent" rows) and then optionally hide the original data column.)

Unfortunately one of the most useful functions that is missing from Excel is the equivalent of the Google Sheets JOIN(delim<delimiter>,range<range>) function.

Office 365 finally has a TEXTJOIN function that does the same thing.

But for some reason they refuse to port the 10 lines of code into standard Excel... :-(

So this is what most of us do when we want a generic (USEFUL) concatenate function :
Code: 
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Function DelimConcat (inRange As Range, Delim as String)
    Dim result as String 
    result = ""
    Dim entry as variant
    For Each entry In inRange
        If Not IsEmpty(entry.Value) Then
            result = result & entry.Value & Delim
        End If
    Next
    If Len(result) > 0 Then
        result = Left$(result, Len(result)- Len(Delim))   'Drop trailing comma
    End if
    DelimConcat = result
End Function
</code>You can then use a =DELIMCONCAT(INDIRECT("C" & row() + 1 & ":C" & MATCH("variable",D3:D9999)-1),",") in place of the concatenate() in your sample above.

(Note however that will require the last entry in Column D to be an extra "variation"... so that the search down MATCH above can find a final row).

Cheers,
Warren K.</range></delimiter>
 
Upvote 0
tdh777 said:
Hi Andy,

Could another way to describe the logic for this extra part be "Concatenate the entries in all the Child Cells between here and the next "variable" (Parent) Row ?

By the way, what you currently have proposed would be a circular reference on the the child entries.



IE either you are only putting this formula onto the Parent rows (bold in the example data) or else you will get a circular reference on the child rows. (The IF(false) entry of C2 going into Cell C2 is not possible...)

(or you could have another column that SHOWS this data (either the same as the original data or the concatenated data for "parent" rows) and then optionally hide the original data column.)

Unfortunately one of the most useful functions that is missing from Excel is the equivalent of the Google Sheets JOIN(delim<delimiter>,range<range>) function.

Office 365 finally has a TEXTJOIN function that does the same thing.

But for some reason they refuse to port the 10 lines of code into standard Excel... :-(

So this is what most of us do when we want a generic (USEFUL) concatenate function :
Code: 
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Function DelimConcat (inRange As Range, Delim as String)
    Dim result as String 
    result = ""
    Dim entry as variant
    For Each entry In inRange
        If Not IsEmpty(entry.Value) Then
            result = result & entry.Value & Delim
        End If
    Next
    If Len(result) > 0 Then
        result = Left$(result, Len(result)- Len(Delim))   'Drop trailing comma
    End if
    DelimConcat = result
End Function
</code>You can then use a =DELIMCONCAT(INDIRECT("C" & row() + 1 & ":C" & MATCH("variable",D3:D9999)-1),",") in place of the concatenate() in your sample above.

(Note however that will require the last entry in Column D to be an extra "variation"... so that the search down MATCH above can find a final row).

Cheers,
Warren K.</range></delimiter>
Click to expand...


Thanks, thankfully I do have access to office 365 so am able to use the textjoin. Still getting a bit mixed up along the whole indirect part though, and some of the values are duplicated, is it possible to get textjoin to not list duplicates?

So the values below the bold text need to be "textjoined" into the bold text, comma deliminated. I will use a seperate collumn to do this to avoid the circular reference, and was thinking something of the lines of this.. (my bold text is now 0) (my bold text is now AN on the spreadsheet i am using)

=IF(AN=0,Textjoin(range, delimination etc),AN)

So if it is zero do the text join, if not put the existing value in it. Just can't work out how to get the textjoin to do a range of the values below the 0 cell and stop at the next 0 cell?
 
Upvote 0
Just can't work out how to get the textjoin to do a range of the values below the 0 cell and stop at the next 0 cell?
Click to expand...


Hi Andy,

Already gave you that in the previous answer.
You can then use a =DELIMCONCAT(INDIRECT("C" & row() + 1 & ":C" & MATCH("variable",D3:D9999)-1),",") in place of the concatenate() in your sample above.

(Note however that will require the last entry in Column D to be an extra "variable"... so that the search down MATCH above can find a final row).
Click to expand...


The text in Blue generates a range from 1 after the current row, down to the row where the next "Variable" is found in Column D. So in the example data that is a range "C3:C4" if placed on Row 2, and "C6:C8" if placed on Row 5 (and there is an extra dummy "variable" in Cell D9 to finish the list).

Because it generates a String as above, then INDIRECT("C3:C4") is the same as entering C3:C4 wherever a Range is required in a formula.

So you already have all the answers you need.

Warren.

 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Appenda data from one sheet to another
Replies
4
Views
599
MASOODAHMAD
M
E
  • Question Question
INDEX / MATCH with the LOOKUP_ARRAY for the MATCH variable. Uses INDIRECT
Replies
8
Views
924
XYZ1234
X
SimonHughes
  • Question Question
Two series of forms controls on one sheet
2
Replies
12
Views
845
SimonHughes
SimonHughes

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top