2

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

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

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

5
  • Welcome to Superuser! "I am trying to keep a list in excel updated dynamically", is it B3:B8? When updating should the item received or entered be added or removed from the list? Aug 15, 2020 at 10:01
  • To whom does the name in the record of trades refer? The giver or receiver? Unless you record both, you won't be able to dynamically update the list as (presumably) items must be removed when given and added when received. Aug 15, 2020 at 10:31
  • Hi Reddy. Yes the dynamic list is B3:B8. So when the first condition is met – A3 (Name) matched in D:D – the formula looks to run a second match of the item listed against that match by Gina in E:E (say the second instance of lemon) and swaps out the lemon in the list B3:B8, with orange. The difficulty I'm having is figuring out how to get that new item to remain in the list to be traded itself in the future.
    – Jimmy
    Aug 15, 2020 at 12:10
  • @FlexYourData - so all the people in D:D will have their own shopping baskets so their own lists that need to be dynamically updated based on the trades that are recorded in E:E (trading item out) and F:F (trading item in). And yes correct, items need to be removed when given and swapped with the one received. This is what I'm struggling to do with formulas alone so thought I'd ask here. I'm confident there's a way to do it with scripts, but I'd prefer not to.
    – Jimmy
    Aug 15, 2020 at 12:17
  • Hi @Jimmy ,, check my post I've solved the issue , is the best I can produced WITHOUT VBA MACRO,, read carefully the explanations & methods to apply,,, hope this help you ☺ Aug 16, 2020 at 8:15

1 Answer 1

0

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

enter image description here


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.

2
  • Amazing! Thank you Rajesh. This is exactly what I needed. So it's actually the 3rd list (the grocers list) that is key. Thanks again.
    – Jimmy
    Aug 20, 2020 at 14:23
  • Glad to help U @Jimmy,, keep asking ☺ Aug 21, 2020 at 4:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.