Adding prices to a list

[Jetx227]

Hey guys, I'm not great at excel so maybe one of you can offer me a hand. So I have a list of items on a sheet in column A (about 20,000 of them). In column B I want to input the unit prices for each item, but I don't want to scroll down and find the item to input it. There's 2 possible solutions that I could think of (and don't know how to do) but if you have another that you think could work, I'll gladly take any advice you can offer. My first idea was just finding a way to search the list and bring that row to the top with the item that you search so you can input the price. The other idea I had is a little more complicated (but would be preferable if it can be done). So not every item on the list is needed, just the ones that are used for the current job that I'm doing. The items that I would be using for the job would be inputted into a separate sheet (column A of that sheet). Is there a way to hide the rows of the items I'm not using and only have the rows with the items for the job I'm working on shown?

Thanks for the help guys!

 

[Chameleon64]

Taking your second idea a bit further, use VLOOKUP in your main table to check if the item matches any item in the list used in the current job.

In Sheet1 column C type the following formula and copy it to the end of your list. If your list of current items is on Sheet2, then:

=IFERROR(VLOOKUP(A2,Sheet2!A:A,1,FALSE),"")

VLOOKUP will produce an error if it can't find the item so we use IFERROR to produce a blank if it can't be found, or the name of the item if it can be found.

Then filter your main table so that only non-blanks appear in column C (I've assumed your main table has a header row).

 

[Jetx227]

Taking your second idea a bit further, use VLOOKUP in your main table to check if the item matches any item in the list used in the current job.

In Sheet1 column C type the following formula and copy it to the end of your list. If your list of current items is on Sheet2, then:

=IFERROR(VLOOKUP(A2,Sheet2!A:A,1,FALSE),"")

VLOOKUP will produce an error if it can't find the item so we use IFERROR to produce a blank if it can't be found, or the name of the item if it can be found.

Then filter your main table so that only non-blanks appear in column C (I've assumed your main table has a header row).

I think this would have worked great but unfortunately it crashed my excel lol. Is it possible there is too many cells it's trying to sort through and that caused it to crash?

 

[Chameleon64]

Shouldn't be the cause - I just tried VLOOKUP with a table of 22,000 records and it took a few seconds to recalculate but it didn't crash.

 

[Jetx227]

I think this would have worked great but unfortunately it crashed my excel lol. Is it possible there is too many cells it's trying to sort through and that caused it to crash?

Update: Excel finally loaded, but one thing I noticed is that this doesn't automatically update every time that I add a new item (we add new items in as they come up in the job constantly). I don't know if you or anyone had a coding idea for this but we have a macro that runs when we put new items in to sheet 2 that makes a new sheet and names it after that specific item. I could just throw some lines in there but I dont know what to put in if anyone has ideas. Thanks!

 

[Jetx227]

Shouldn't be the cause - I just tried VLOOKUP with a table of 22,000 records and it took a few seconds to recalculate but it didn't crash.

It was when i tried to filter it