Formula, Function, VBA or even possible??

[This2willpass]

Hello All,

I have a store list with prices and I am hoping to find a formula to help.

In the attached file there are 4 sample columns. Flavor, Item #, Store, Price.

The issue I have is when adding a new flavor it does not contain a price. I would like it to be able to copy the price from the original flavors to the corresponding sizes of the new flavors(Original Small = Green Small). However given that each store has a different price it would need to be contingent to a matching store number. I don't know if item number helps in this at all.

I understand that I can simply go in and copy and paste however My sheet has 250k+ rows from 100 different stores so i made the excel sample as simple as I could.

Any and all insight is greatly appreciated. Thank you in advance.

Flavor	Item#	Store	Price
Original		NY
Small	101	NY	2.00
Medium	102	NY	3.00
Large	103	NY	4.00
***********		NY
Green		NY
Green Small	111	NY
Green Medium	112	NY
Green Large	113	NY
***********		NY
Blue		NY
Blue Small	121	NY
Blue Medium	122	NY
Blue Large	123	NY
Original		CA
Small	101	CA	2.15
Medium	102	CA	3.15
Large	103	CA	4.15
***********		CA
Green		CA
Green Small	111	CA
Green Medium	112	CA
Green Large	113	CA
***********		CA
Blue		CA
Blue Small	121	CA
Blue Medium	122	CA
Blue Large	123	CA

<tbody>
</tbody>

 

[Sinon]

If you have the prices in another list you can pull those through with a formula. Try VLOOKUP (Using VLOOKUP in Excel) or INDEX/MATCH (How to Use INDEX MATCH)

 

[AlanY]

try this in Col D

=SUMIFS(D:D,A:A,TRIM(RIGHT(SUBSTITUTE(A8," ",REPT(" ",LEN(A8))),LEN(A8))),C:C,C8)

or in Col E to avoid the circular references

 

[This2willpass]

try this in Col D

=SUMIFS(D:D,A:A,TRIM(RIGHT(SUBSTITUTE(A8," ",REPT(" ",LEN(A8))),LEN(A8))),C:C,C8)

or in Col E to avoid the circular references

Hmm this seemed to work on the sample but when tried plugging it into my actual sheet I simply get 0.

I guess in essence what I am trying to achieve is to generate a price into column D based off the price from a row with a certain "Item #"(column 2)(always the same item number for each store. in this case 101 = small) and matching Store (column C) to the store listed in row where price is to be generated. Please note all stores have the same item #'s just different prices.

So first search for specific item # then match Store name/number and copy the price this row has into the new cell.

Thanks again

 

[AlanY]

can you post a small sample of the actual sheet so that we can have a look of the problem?

 

[This2willpass]

can you post a small sample of the actual sheet so that we can have a look of the problem?

This is directly from the excel sheet. 11 columns A-K. The first table references are (Flavor=Item name, Item #=Item#(or Item Seq), Store=Price Tier(or Price Tier Seq), Price=Price1). Column D(NLU Group) through Column G(Price Group Seq) should not play any role as these remain the same through the entire spreadsheet. Let me know if you need any more info. Thanks again AlanY.

Item name	Item Seq	Item #	NLU Grp	NLU #	Price Group	Price Grp Seq	Price Tier	Price Tier Seq	Price Seq	Price 1
**ORIGINAL**	1	301000			1 Default Price Group	1	ATL	51	2620811
SM ORIG	2	301001			1 Default Price Group	1	ATL	51	2620812	3.85
MD ORIG	1116	301002			1 Default Price Group	1	ATL	51	2620813	4.95
MD ORIG W/Top	1039	301003			1 Default Price Group	1	ATL	51	2620814	6.55
LG ORIG	1117	301004			1 Default Price Group	1	ATL	51	2620815	7.15
LG ORIG W/Top	1118	301005			1 Default Price Group	1	ATL	51	2620816	8.75
SM ORIG W/Top	1272	301006			1 Default Price Group	1	ATL	51	2620817	5.45
MN ORIG	1452	301007			1 Default Price Group	1	ATL	51	2620818	3.25
MN ORIG W/Top	1453	301008			1 Default Price Group	1	ATL	51	2620819	3.85
**WATERMELON**	1853	301100			1 Default Price Group	1	ATL	51	2620821
MN WMLN	1860	301101			1 Default Price Group	1	ATL	51	2620822
MN WMLN W/Top	1861	301102			1 Default Price Group	1	ATL	51	2620823
SM WMLN	1854	301103			1 Default Price Group	1	ATL	51	2620824
SM WMLN W/Top	1859	301104			1 Default Price Group	1	ATL	51	2620825
MD WMLN	1855	301105			1 Default Price Group	1	ATL	51	2620826
MD WMLN W/Top	1856	301106			1 Default Price Group	1	ATL	51	2620827
LG WMLN	1857	301107			1 Default Price Group	1	ATL	51	2620828
LG WMLN W/Top	1858	301108			1 Default Price Group	1	ATL	51	2620829
**ORIGINAL**	1	301000			1 Default Price Group	1	Colorado	13	2676282
SM ORIG	2	301001			1 Default Price Group	1	Colorado	13	2676283	3.5
MD ORIG	1116	301002			1 Default Price Group	1	Colorado	13	2676284	4.5
MD ORIG W/Top	1039	301003			1 Default Price Group	1	Colorado	13	2676285	5.95
LG ORIG	1117	301004			1 Default Price Group	1	Colorado	13	2676286	6.5
LG ORIG W/Top	1118	301005			1 Default Price Group	1	Colorado	13	2676287	7.95
SM ORIG W/Top	1272	301006			1 Default Price Group	1	Colorado	13	2676288	4.95
MN ORIG	1452	301007			1 Default Price Group	1	Colorado	13	2676289	2.95
MN ORIG W/Top	1453	301008			1 Default Price Group	1	Colorado	13	2676290	3.5
**WATERMELON**	1853	301100			1 Default Price Group	1	Colorado	13	2676292
MN WMLN	1860	301101			1 Default Price Group	1	Colorado	13	2676293
MN WMLN W/Top	1861	301102			1 Default Price Group	1	Colorado	13	2676294
SM WMLN	1854	301103			1 Default Price Group	1	Colorado	13	2676295
SM WMLN W/Top	1859	301104			1 Default Price Group	1	Colorado	13	2676296
MD WMLN	1855	301105			1 Default Price Group	1	Colorado	13	2676297
MD WMLN W/Top	1856	301106			1 Default Price Group	1	Colorado	13	2676298
LG WMLN	1857	301107			1 Default Price Group	1	Colorado	13	2676299
LG WMLN W/Top	1858	301108			1 Default Price Group	1	Colorado	13	2676300

<tbody>
</tbody>

 

[acctgdana]

This is directly from the excel sheet. 11 columns A-K. The first table references are (Flavor=Item name, Item #=Item#(or Item Seq), Store=Price Tier(or Price Tier Seq), Price=Price1). Column D(NLU Group) through Column G(Price Group Seq) should not play any role as these remain the same through the entire spreadsheet. Let me know if you need any more info. Thanks again AlanY.

Alan, Can you add another tab to list stores, sizes, and prices? So, in tab/worksheet2, Col A=formula for Col B current row & Col C current row (ex =B2&C2), Col B=Store (Price tier/Col H from current table), Col C=size (SM, MD, LG, MN, and the same with T at the end to indicate topping prices SMT, MDT, LGT, MNT), col D=price for combo of col store and size in that row. Once that table is generated, the problem is a a lot easier to resolve.

You can do a vlookup from your current sheet for price for all items. Formula, using row 2 of data: =IF((LEFT(A2,2))="**","N/A",IF(RIGHT(A2,1)="p",VLOOKUP((H2&(LEFT(A2,2))&"T"),Sheet2!A:D,4,0),VLOOKUP((H2&(LEFT(A2,2))),Sheet2!A:D,4,0)))

 

[AlanY]

try this in Col L, in L4 and copy down

=IF(LEFT(A4,1)="*","",SUMIFS(K:K,A:A,IF(ISERROR(SEARCH("/Top",A4)),LEFT($A4,2)&" ORIG",LEFT($A4,2)&" ORIG W/TOP"),H:H,H4))

if you're happy with the numbers you can cut and paste values back to Col K