Mapping one sheet to another?

[AndyGray]

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??

 

[tdh777]

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.

=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.

 

[AndyGray]

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]

 

[tdh777]

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)

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 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>

 

[AndyGray]

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 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>

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?

 

[tdh777]

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?

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).

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.