Expand the current formula to include additional cells

OK. So, I threw yuour formula out. It was FAR too slow.

I changed the header in M1 to match corresponding the value in Background.

I replaced it with this:

=IFERROR(INDEX(Background!$A:$H,AGGREGATE(15,6,ROW(Background!$A$2:$A$100)/(Background!$A$2:$A$100=LOOKUP(ROW($B$2:$B$100),ROW($B$2:$B$100)/($B$2:$B$100>0),$B$2:$B$100)),ROWS(H$2:H2)),MATCH(H$1,Background!$A$1:$H$1,0))&"","")

in H2, copied across and down (note adjusy bits in red to suit your real data. I can help further here, after we agree that it's working.

I mocked up data for a second container (1234). Add that container number to the next available row in column B... which is B7

Don't.

You enter data on the NEXT AVAILABLE ROW... which is row 7. I can add some conditional formatting to highlight the next available row. Would that help?

Well you've confused me. So, Start again...

What do you want to see? Where do you want to see it? Where should it come from?

Still confused. So... baby steps time...

See sheet. You can now enter stuff in B3. What do you want to enter in B3 and what happens next. Amend the sheet to provide an appropriate entry in B3... and manually update sheet to show what you then expect a formula to do...

Who can tell? maybe I'm not good at understanding!!

I have added red formatting to flag-up SKUs that are NOT in the contaiuner (B7 & B8). Is Red OK... if not, what can I use?

1. How many containers per sheet. if more than 1, how many rows per container?

2. may need to control the zeros in the SKU lists. these may cause problems. Are SKUs always numbers, or can they be alphanumeric?

3. I do not understand where the numbers in N come from.

1. Clear. no problem.

2. Fixed. They now return blanks.

3. You said:

the top one with 6 units has 1 unit of Supplier SKU ending in 599 & 1 unit of UPC ending in 875 entered on the input section resulting in a discrepancy of -4

What input section? Where is it?

4. Could do Zoom, if needed!!

For Demogorgan's benefit: needless to say, if you DO do Zoom, please don't forget to report back here. The help here is free, of course, and should be here to benefit all.

Of course - it was more to aid in the explanation of what I am trying to acheive as I am not the best communicator through typing..

Definitely - some of us prefer visuals!

Final version??

1. I moved the cell containing the container number. Since there's only one, it should be in a more prominent position.

2. I accidentally deleted all your shading... I have put some back again. So, don't be surprised if it looks a bit different!!!

3. All of the Conditional formatting and formulae go down 1000 rows, more than enough.

4. Non-existing SKUs are highlighted twice, once in cols B& C and in a consolidated list that appears from Q3 downwards. The "Not Found" in C can be changed to something shorter... even to an "X" in a hidden column, but something is needed to trigger the formula in Q.

5. Finally, I think I understand this.

6. Try to break it. There may be some bugs in it...

Well... I said the formulae were all good to go... They weren't. that formula stopped at row 15. Now fixed.

Hopefully you'll find it all a bit faster than the original!!

There's a commercial services subforum here where users can pay for services from the most active members. I do not participate there. Ever.

I muck around here to keep the little grey cells active... for fun basically. Especially during Lockdown 3...

I don't need the money and I always have concerns that if I accept any payments of any kind I am also accepting an element of legal liability in the event that something goes spectacularly belly-up as a consequence of one of my formulae not going down a column far enough... or whatever.

if anything else goes wrong, just shout. I'll be here off-and-on tomorrow. A delivery of fresh malted barley arrived here yesterday... so it's beer-making time tomorrow morning.

for now,



You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

Originally Posted by Glenn Kennedy

Well... I said the formulae were all good to go... They weren't. that formula stopped at row 15. Now fixed.

Hopefully you'll find it all a bit faster than the original!!

It is much faster thank you! - I still have the same issue on the final one when I input the SKU's as the screen shot it doesnt mark them as not found?

Show me... It looks OK to me. Modify this and repost.

Soaking wet after getting caught in a torrential downpour whilst out walking....

Now you see why I am not interested in any recompense...I can go "Meh!!" and walk away.

The problem was caused by the fact that all of those short strings were components of one or more of the full SKU codes. Now fixed by adding (in the formula) a space before and after each term.

Incidentally, i didn't mention it... I changed the formulas returning the SKUs again to return the #N/A error instead of blanks. The blanks interfered elsewhere. The reason you can't see them is that I then used conditional formatting to make the font shade the same as the background shading.

One last thing... well, two actually. I forgot to fix a tiny glitch on the red formatting. Now applied only to column C. I also noticed that your file was suffering a bit from Excel bloat, possibly as a result of too much conditional formatting. It is now a lot smaller (about 20% of original size).

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.