Trouble Understanding Which Formula is needed

gravanoc

gravanoc

Active Member
Joined
Oct 20, 2015
Messages
351
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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
 
Sort by date Sort by votes
Re: Is this solvable with built-in functions as is?

Should VBA be used?
 
Upvote 0

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Re: Is this solvable with built-in functions as is?

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

Source
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#4472C4]SKU[/td][td=bgcolor:#4472C4]NAME[/td][td=bgcolor:#4472C4]IMAGE[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9E1F2]BS02/4-BSP[/td][td=bgcolor:#D9E1F2]BACKSPLASH/MURAL: Modular Hand Painted ~ Vino Veritas Grapes Design (4 Tiles)[/td][td=bgcolor:#D9E1F2]BS02_4-BSP.jpg[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]BS02_4-BSP_2.jpg[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9E1F2]BS02/6-BSP[/td][td=bgcolor:#D9E1F2]BACKSPLASH/MURAL: Modular Hand Painted ~ Vino Veritas Grapes Design (6 Tiles)[/td][td=bgcolor:#D9E1F2]BS02_6-BSP.jpg[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]BS02_6-BSP_2.jpg[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9E1F2][/td][td=bgcolor:#D9E1F2][/td][td=bgcolor:#D9E1F2]BS02_6-BSP_3.jpg[/td][/tr]

[tr=bgcolor:#FFFFFF][td]BS05/4-BSP[/td][td]BACKSPLASH/MURAL: Modular Hand Painted ~ Tuscan Lemon Design (4 Tiles)[/td][td]BS05_5-BSP.JPG[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9E1F2][/td][td=bgcolor:#D9E1F2][/td][td=bgcolor:#D9E1F2]BS05_5-BSP_2.JPG[/td][/tr]

[tr=bgcolor:#FFFFFF][td]BS24X16-VIT[/td][td]BACKSPLASH/MURAL: Toscana Grapes (Six Tiles) [R][/td][td]BS24X16-VIT.jpg[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9E1F2][/td][td=bgcolor:#D9E1F2][/td][td=bgcolor:#D9E1F2]BS24X16-VIT_2.jpg[/td][/tr]

[tr=bgcolor:#FFFFFF][td]BS25-VIT[/td][td]BACKSPLASH/MURAL: Toscana Grapes 2[/td][td]BS25-VIT.jpg[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#D9E1F2]BS26-VIT[/td][td=bgcolor:#D9E1F2]BACKSPLASH/MURAL: Toscana Grapes 3[/td][td=bgcolor:#D9E1F2]BS26-VIT.jpg[/td][/tr]

[tr=bgcolor:#FFFFFF][td]BS27-VIT[/td][td]BACKSPLASH/MURAL: Toscana Grapes 4[/td][td]BS27-VIT.jpg[/td][/tr]
[/table]

Result
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]SKU[/td][td=bgcolor:#70AD47]NAME[/td][td=bgcolor:#70AD47]Image.1[/td][td=bgcolor:#70AD47]Image.2[/td][td=bgcolor:#70AD47]Image.3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]BS02/4-BSP[/td][td=bgcolor:#E2EFDA]BACKSPLASH/MURAL: Modular Hand Painted ~ Vino Veritas Grapes Design (4 Tiles)[/td][td=bgcolor:#E2EFDA]BS02_4-BSP.jpg[/td][td=bgcolor:#E2EFDA]BS02_4-BSP_2.jpg[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]BS02/6-BSP[/td][td]BACKSPLASH/MURAL: Modular Hand Painted ~ Vino Veritas Grapes Design (6 Tiles)[/td][td]BS02_6-BSP.jpg[/td][td]BS02_6-BSP_2.jpg[/td][td]BS02_6-BSP_3.jpg[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]BS05/4-BSP[/td][td=bgcolor:#E2EFDA]BACKSPLASH/MURAL: Modular Hand Painted ~ Tuscan Lemon Design (4 Tiles)[/td][td=bgcolor:#E2EFDA]BS05_5-BSP.JPG[/td][td=bgcolor:#E2EFDA]BS05_5-BSP_2.JPG[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]BS24X16-VIT[/td][td]BACKSPLASH/MURAL: Toscana Grapes (Six Tiles) [R][/td][td]BS24X16-VIT.jpg[/td][td]BS24X16-VIT_2.jpg[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]BS25-VIT[/td][td=bgcolor:#E2EFDA]BACKSPLASH/MURAL: Toscana Grapes 2[/td][td=bgcolor:#E2EFDA]BS25-VIT.jpg[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]BS26-VIT[/td][td]BACKSPLASH/MURAL: Toscana Grapes 3[/td][td]BS26-VIT.jpg[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]BS27-VIT[/td][td=bgcolor:#E2EFDA]BACKSPLASH/MURAL: Toscana Grapes 4[/td][td=bgcolor:#E2EFDA]BS27-VIT.jpg[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]
[/table]
Code: 
// 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 :)
 
Last edited:
Upvote 0
Re: Is this solvable with built-in functions as is?

Thanks, that's awesome.
 
Upvote 0
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
 
Upvote 0
Re: Is this solvable with built-in functions as is?

here is more flexible version:

Code: 
[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]
 
Upvote 0
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!
 
Upvote 0
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
ABCDEF
1SKUNAMEImage 1 FileImage 2 FileImage 3 File
2BS02/4-BSPBACKSPLASH/MURAL: Modular Hand Painted ~ Vino Veritas Grapes Design (4 Tiles)BS02_4-BSP.jpgBS02_4-BSP_2.jpg
3BS02/6-BSPBACKSPLASH/MURAL: Modular Hand Painted ~ Vino Veritas Grapes Design (6 Tiles)BS02_6-BSP.jpgBS02_6-BSP_2.jpgBS02_6-BSP_3.jpg
4BS05/4-BSPBACKSPLASH/MURAL: Modular Hand Painted ~ Tuscan Lemon Design (4 Tiles)BS05_5-BSP.JPGBS05_5-BSP_2.JPG
5BS24X16-VITBACKSPLASH/MURAL: Toscana Grapes (Six Tiles) [R]BS24X16-VIT.jpgBS24X16-VIT_2.jpg
6BS25-VITBACKSPLASH/MURAL: Toscana Grapes 2BS25-VIT.jpg
7BS26-VITBACKSPLASH/MURAL: Toscana Grapes 3BS26-VIT.jpg
8BS27-VITBACKSPLASH/MURAL: Toscana Grapes 4BS27-VIT.jpg
WEB
 
Upvote 0
@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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Pointing to appropriate cells via formula with variables
Replies
3
Views
829
roxdrob
R
M
  • Question Question
Another Sum If By Color Question
Replies
0
Views
832
McSkillet
M
R
  • Question Question
VBA code to change specific text in a cell's results that contains a formula to bold and change the color
Replies
8
Views
10K
asad
A
R
  • Question Question
Need Help with Homework Assignment ASAP PLEASE!
Replies
3
Views
1K
Sal Paradise
S
J
  • Question Question
Dynamic update of column in a formula?
Replies
3
Views
786
Domenic
D

Forum statistics

Threads
1,224,817
Messages
6,181,147
Members
453,021
Latest member
Justyna P

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