Creating a unique lookup(2)

I recently implemented a formula into my cycle count program. The formula looks as follows
=INDEX('Count Status'!$B$8:$B$1667,SMALL(IF(('Items to Count'!$B$1='Count Status'!$H$8:$H$1667)*COUNTIF('Count Status'!$B$8:$B$1667,"<="&'Count Status'!$B$8:$B$1667),ROW('Count Status'!$B$8:$B$1667)-ROW('Count Status'!$B$8)+1),ROWS(B$6:B1348)))

However, after using this formula I have used all of my computer companies RAM and have been tasked to find a new way to go about using my spreadsheet. Essentially, when I have a item change from do not count to count on the count status page, I would like the Item to count page to reflect that so that I can then issue out those counts.

I really need some help with this one if anyone has any suggestions. Previously this topic was posted about a month ago under creating a unique lookup.

Thanks

I am not what you are doing with this part: COUNTIF('Count Status'!$B$8:$B$1667,"<="&'Count Status'!$B$8:$B$1667) and if it is needed. The results will always be positive, since you are compare the same column to itself.

So assuming that is a redundant section, what you can do to lessen the Ram usage is add a helper to the Count Status sheet that creates a cumulative count of matches.

In an empty column in Count Status, say column I, starting in row 8 enter formula (in I8):

=IF(H8='Items to Count'!$B$1,COUNT(I$7:I7)+1,"")

copied down

Then in the Items to Count sheet, enter formula:

=IFERROR(INDEX('Count Status'!$B$8:$B$1667,MATCH(ROWS($A$1:$A1),'Count Status'!$I$8:$I$1667,0)),"")

copied down as far as necessary.

Note that I added the IFERROR() function. This will return a blank cell instead of an error after the actual results needed are returned.

These formulas do not need you to confirm with CTRL+SHIFT+ENTER. Just ENTER as normal.

I received that formula from another member on my previous thread, and it was working great until I realized how slow it was to run 1667 lines or more depending on my inventory level.

As of right now, the formulas you gave me, which I tweaked just to fit where my tables are as follows:
=IFERROR(INDEX('Count Status'!$B$8:$B$1667,MATCH(ROWS($B$1:$B$1),'Count Status'!$I$8:$I$1667,0)),"")
=IF(H8='Items to Count'!$B$1,COUNT(I$7:I7)+1,"")
But, this only works for the first raw material. When I copy down. I do not see the name or description of my raw material. Previously, the formula would take the item name and description from count status and put them on items to count page depending. Now my table in items to count has the first raw material only and it's description. I may be interpreting the formula wrong, but I think we are on the right direction.

NBVC let me know if I should be clearer on what I am trying to get out of my spreadsheet

Can you upload a non-confidential sample workbook that I can use to demonstrate?

Sure thing,

So it look similar to this, there are other sheets but for this formula not necessary to see and also somewhere betweeen 1500-3000 lines depending on inventory so I only made 2 as I can copy down. So what I was trying to accomplish is when the count status page had the date that the item was counted, it would change the status on that page to do not count. So on another sheet I was looking for only items that I could count. This way, I could manage what needed to be counted, and what was also close to needing to be counted again. I hope this helps.!

The formulas I gave you start referencing row 8 because that was the original references in your question, but actually you should be starting in row 2 per your attachment.... so, first, the formula you have in H2 should probably reference the same ros, so it should be: =IF(G2>125,"Count","Do Not Count")

Then the formula in I2 of Count Status should be:

=IF(H2='Items to Count'!$B$1,COUNT(I$1:I1)+1,"")

copied down.

and in the other page, formula in B6:

=IFERROR(INDEX('Count Status'!A$2:A$3,MATCH(ROWS($A$1:$A1),'Count Status'!$I$2:$I$3,0)),"")

copied down and to next column (adjust ranges to suit your first sheet).

Can't you use a pivot table? Clean. Easy. Fast. Data range is the table in 'Count Status'. Since you're using tables, the pivot range will automatically adjust to fit if the records or fields change in the data source. You can then refresh the pivot manually or with a simple macro.

Thank you NBVC for clarifying. I adjusted the formula and it seems to work perfectly and alot quicker! I am very happy to see this formula working. This is a slick spreadsheet now I enjoy using it.

I will also try your solution rBear and see which one works more efficiently. I am not too skilled with pivot tables and have been waiting to exlpore them maybe now is the time.

Thank you both for your help!