Trouble Understanding Which Formula is needed

[gravanoc]

Hi, I'm having a major headache right now and can't think straight on this. I've tried a couple of solutions, but still can't wrap my head around what is needed.

Basically, I am trying to return a value from the first tab contingent on whether or not the value underneath cell A1 on that tab is blank or not. It's hard to describe, but I put an explainer in the cell adjacent to where the formula would be.

https://drive.google.com/open?id=1_6ktCot2wQ33RsumUNz7NaA2z6DAePNZ

 

[gravanoc]

Re: Is this solvable with built-in functions as is?

Should VBA be used?

 

[sandy666]

Re: Is this solvable with built-in functions as is?

with PowerQuery (Get&Transform) which is built-in to Excel 2016 and above:

Source

SKU	NAME	IMAGE
BS02/4-BSP	BACKSPLASH/MURAL: Modular Hand Painted ~ Vino Veritas Grapes Design (4 Tiles)	BS02_4-BSP.jpg
		BS02_4-BSP_2.jpg
BS02/6-BSP	BACKSPLASH/MURAL: Modular Hand Painted ~ Vino Veritas Grapes Design (6 Tiles)	BS02_6-BSP.jpg
		BS02_6-BSP_2.jpg
		BS02_6-BSP_3.jpg
BS05/4-BSP	BACKSPLASH/MURAL: Modular Hand Painted ~ Tuscan Lemon Design (4 Tiles)	BS05_5-BSP.JPG
		BS05_5-BSP_2.JPG
BS24X16-VIT	BACKSPLASH/MURAL: Toscana Grapes (Six Tiles) [R]	BS24X16-VIT.jpg
		BS24X16-VIT_2.jpg
BS25-VIT	BACKSPLASH/MURAL: Toscana Grapes 2	BS25-VIT.jpg
BS26-VIT	BACKSPLASH/MURAL: Toscana Grapes 3	BS26-VIT.jpg
BS27-VIT	BACKSPLASH/MURAL: Toscana Grapes 4	BS27-VIT.jpg

Result

SKU	NAME	Image.1	Image.2	Image.3
BS02/4-BSP	BACKSPLASH/MURAL: Modular Hand Painted ~ Vino Veritas Grapes Design (4 Tiles)	BS02_4-BSP.jpg	BS02_4-BSP_2.jpg
BS02/6-BSP	BACKSPLASH/MURAL: Modular Hand Painted ~ Vino Veritas Grapes Design (6 Tiles)	BS02_6-BSP.jpg	BS02_6-BSP_2.jpg	BS02_6-BSP_3.jpg
BS05/4-BSP	BACKSPLASH/MURAL: Modular Hand Painted ~ Tuscan Lemon Design (4 Tiles)	BS05_5-BSP.JPG	BS05_5-BSP_2.JPG
BS24X16-VIT	BACKSPLASH/MURAL: Toscana Grapes (Six Tiles) [R]	BS24X16-VIT.jpg	BS24X16-VIT_2.jpg
BS25-VIT	BACKSPLASH/MURAL: Toscana Grapes 2	BS25-VIT.jpg
BS26-VIT	BACKSPLASH/MURAL: Toscana Grapes 3	BS26-VIT.jpg
BS27-VIT	BACKSPLASH/MURAL: Toscana Grapes 4	BS27-VIT.jpg

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FillD = Table.FillDown(Source,{"SKU", "NAME"}),
    Group = Table.Group(FillD, {"SKU", "NAME"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Image", each Table.Column([Count],"IMAGE")),
    Extract = Table.TransformColumns(List, {"Image", each Text.Combine(List.Transform(_, Text.From), "="), type text}),
    Split = Table.SplitColumn(Extract, "Image", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Image.1", "Image.2", "Image.3"})
in
    Split

and no, this is not a vba

 

[gravanoc]

Re: Is this solvable with built-in functions as is?

Thanks, that's awesome.

 

[sandy666]

Re: Is this solvable with built-in functions as is?

You are welcome

btw. this is a simple version because I don't know how many columns of images will be

 

[sandy666]

Re: Is this solvable with built-in functions as is?

here is more flexible version:

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FillD = Table.FillDown(Source,{"SKU", "NAME"}),
    Group = Table.Group(FillD, {"SKU", "NAME"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Image", each Table.Column([Count],"IMAGE")),
    Extract = Table.TransformColumns(List, {"Image", each Text.Combine(List.Transform(_, Text.From), "="), type text}),
    SplitCount = Table.AddColumn(Extract, "SCount", each List.Count(Text.Split([Image],"="))),
    MaxCount = List.Max(SplitCount[SCount]),
    Split = Table.SplitColumn(Extract, "Image", Splitter.SplitTextByDelimiter("="), MaxCount)
in
    Split[/SIZE]

 

[gravanoc]

Re: Is this solvable with built-in functions as is?

Thanks again. I will hold onto the new version in case it is needed, but the original works fine too since it's just those 3 columns. I already tested it and it all comes out perfectly. I'll need to study this so I can remember it next time this type of issue comes up. I've used the power query editor before, but just for web extraction using RSS feeds. I literally spent 4 hours trying to figure this thing out so you saved me a lot of time!

 

[Peter_SSs]

Try this.
Formula in C2 (your formula) is copied down.
Formula in D2 is copied across as far as you might ever need and down.

Excel Workbook
	A	B	C	D	E	F
1	SKU	NAME	Image 1 File	Image 2 File	Image 3 File
2	BS02/4-BSP	BACKSPLASH/MURAL: Modular Hand Painted ~ Vino Veritas Grapes Design (4 Tiles)	BS02_4-BSP.jpg	BS02_4-BSP_2.jpg
3	BS02/6-BSP	BACKSPLASH/MURAL: Modular Hand Painted ~ Vino Veritas Grapes Design (6 Tiles)	BS02_6-BSP.jpg	BS02_6-BSP_2.jpg	BS02_6-BSP_3.jpg
4	BS05/4-BSP	BACKSPLASH/MURAL: Modular Hand Painted ~ Tuscan Lemon Design (4 Tiles)	BS05_5-BSP.JPG	BS05_5-BSP_2.JPG
5	BS24X16-VIT	BACKSPLASH/MURAL: Toscana Grapes (Six Tiles) [R]	BS24X16-VIT.jpg	BS24X16-VIT_2.jpg
6	BS25-VIT	BACKSPLASH/MURAL: Toscana Grapes 2	BS25-VIT.jpg
7	BS26-VIT	BACKSPLASH/MURAL: Toscana Grapes 3	BS26-VIT.jpg
8	BS27-VIT	BACKSPLASH/MURAL: Toscana Grapes 4	BS27-VIT.jpg
WEB

 

[Peter_SSs]

@gravanoc
Please do not post the same question multiple times. Questions of a duplicate nature will be locked or deleted, per #12 of the Forum Rules and points 6 & 7 of the Forum Use Guidelines.
In this case, since both threads had replies, I have merged them but please do not do this in the future.

 

[gravanoc]

Thanks Peter, yes I apologize for that.