Update an excel list dynamically without using scripts

Question

I'm a bit stuck and could use some help.

I am trying to keep a list in excel updated dynamically, based on conditions in other cells. Something along the lines of (IF both cell values in A1 and A2 MATCH the cell values in A3 and A4, return A5). I'd ideally like to use just formulas and no scripts, but not sure if it's possible.

Here's a scenario. Gina (A3) has a shopping basket with 6 items in it (B3:B8): Lemon, Toothpaste, Brownie, Hairbrush, Grapes, Sandwich –

Gina's Basket

Gina and her friends like to trade things with each other, and when they do they make a record of each trade (D3:F5) –

record of trades

What I'm trying to do is figure out how to update Gina's shopping basket every time she records a new trade.

And in addition it needs to allow for trading the same item twice (for different items on each occasion) accounting for only the most recent entry traded for that item – in this case she is receiving an orange in her basket –

in this case she is receiving an orange in her basket not, not a cupcake

I've tried various combinations of IF, AND, MATCH and INDEX with duplicate second column, or getting stuck in circular dependencies, and I can't get to the bottom of it.

Here's a link to the worksheet: https://docs.google.com/spreadsheets/d/17J-lX2V1Zs-K7WmsfruqcEJtmElM5rCQTeCLFh8FX1U/edit?usp=sharing

If anyone has any ideas on how to solve this, I would be hugely grateful!

Thanks Jimmy

Answer 1

:: Caveat ::

Since OP doesn't needs VBA MACRO based solution, so in this circumstances the possibility I found is using, few ARRAY (CSE) formula & Conditional Formatting. Possibly, that other may suggest better one.

---

How it works:

• Traded Items and Grocer's List are TABLE.

As soon you add or remove Name or Item from TABLE, Excel will automatically update related data.

• Other than Shoping Basket & Traded Items, you need to create Grocer's List too.

• As soon someone buys any Item, you need to UPDATE the Grocer's Table.

Check the Old Items list, are bought by GINA and others.

• Create one drop down list in cell P17, currently has only 3 name, you may add more.

If you have huge list of buyers then, to get Unique list you may use either of the formulas.

• An array (CSE) formula in cell P28:

   {=IFERROR(INDEX($U$17:$U$30, MATCH(0, INDEX(COUNTIF($P$27:P27, $U$17:$U$30)+($U$17:$U$30=""), ), 0)), "")}
  

• For higher versions you may use this one in P28:

  =UNIQUE(FILTER(U17:U30,U175:U30<>""))

Then use Unique use buyers list for Drop down in cell P17.

• Now use this array (CSE) formula in cell Q17:

  {=IFERROR(INDEX($T$17:$T$30, SMALL(IF(COUNTIF($P$17, $U$17:$U$30)*COUNTIF($U$17:$U$30,"<>"), ROW($T$17:$U$30)-MIN(ROW($T$17:$U$30))+1), ROW(A1)), COLUMN(A1)),"")}
  

• An array (CSE) formula in cell R17:

  {=IFERROR(INDEX($N$17:$N$22, SMALL(IF(COUNTIF($P$17, $L$17:$L$22)*COUNTIF($N$17:$N$22,"<>"), ROW($L$17:$N$22)-MIN(ROW($L$17:$N$22))+1), ROW(A1)), COLUMN(A1)),"")}
  

• Apply Conditional formatting, using below shown formula as New Rule, on Q17:Q25:

  =COUNTIF($N$17:$N$22,Q17)=0

---

N.B.

• Finish array (CSE) formula with Ctrl+Shift+Enter & fill in required direction.

• New Item's list shows the exchanged (Traded ) items.

• Old list has item's in RED, are un-exchanged items, as you can find GINA bought FIVE items ( check the Customer's list in Column U), and exchanged only TWO, are Avocado & Cigars.

• Keep changing Names in cell P17, you get results.

• Adjust cell references in the formula as needed.