- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Excel question for you geniuses
Posted on 4/25/24 at 8:37 am
Posted on 4/25/24 at 8:37 am
First, I'm an idiot when it comes to Excel. I hate it with a passion and try my best to avoid it.
However, my employer does not agree with my assessment and they use it a lot.
I have been appointed to conduct an inventory of all our assets. I have a master spreadsheet with every device and a separate spreadsheet with all the devices I have been able to locate.
How can I bump the two lists against each other to see what I am missing? Our inventory numbers are over 1,400 items. I don't want to hand jam all this shite.
However, my employer does not agree with my assessment and they use it a lot.
I have been appointed to conduct an inventory of all our assets. I have a master spreadsheet with every device and a separate spreadsheet with all the devices I have been able to locate.
How can I bump the two lists against each other to see what I am missing? Our inventory numbers are over 1,400 items. I don't want to hand jam all this shite.
6
Posted on 4/25/24 at 8:44 am to King Crab
Ask ChatGPT to explain it to you - seriously.
It involves using a formula called VLOOKUP that looks up data in a column. You can highlight a cell if it is repeated or missing out of a list. But VLOOKUP is the easiest method, IMO.
It involves using a formula called VLOOKUP that looks up data in a column. You can highlight a cell if it is repeated or missing out of a list. But VLOOKUP is the easiest method, IMO.
Posted on 4/25/24 at 12:48 pm to FortunateSon
Its unfair how well it does at writing macros and formulas to do exactly what you tell it to. I only discovered it this week. Beats the hell out of googling for a specific solution and piecing a macro together like I used to.
Posted on 4/25/24 at 1:42 pm to FortunateSon
Yes, VLOOKUP is what you are looking for. I use that function daily.
Posted on 4/25/24 at 2:26 pm to FortunateSon
I prefer Xlookup generally
Posted on 4/25/24 at 3:53 pm to King Crab
xlookup
This post was edited on 4/25/24 at 3:58 pm
Posted on 4/25/24 at 5:31 pm to King Crab
I use Power BI, but stick with VLOOKUP.
Posted on 4/25/24 at 11:01 pm to King Crab
Does each list have inventory numbers or some other unique identifying data?
If so, a simple countif function can be used to tell you which items in the master list have been inventoried.
=IF(COUNTIF(Inventoried!A$:A$, A2) = 0, "Not Inventoried", "Inventoried")
You would put this formula to the immediate right of you last column of data in your master inventory list. Then copy it down to every row in the master list. It will tell you which items were inventoried and which weren't.
Adjust the Cell References to point to the correct columns
If so, a simple countif function can be used to tell you which items in the master list have been inventoried.
=IF(COUNTIF(Inventoried!A$:A$, A2) = 0, "Not Inventoried", "Inventoried")
You would put this formula to the immediate right of you last column of data in your master inventory list. Then copy it down to every row in the master list. It will tell you which items were inventoried and which weren't.
Adjust the Cell References to point to the correct columns
This post was edited on 4/25/24 at 11:02 pm
Posted on 4/26/24 at 1:51 pm to King Crab
quote:
First, I'm an idiot when it comes to Excel. I hate it with a passion and try my best to avoid it.
Just remember this:
Posted on 4/26/24 at 2:59 pm to FortunateSon
quote:
Ask ChatGPT to explain it to you - seriously. It involves using a formula called VLOOKUP that looks up data in a column. You can highlight a cell if it is repeated or missing out of a list. But VLOOKUP is the easiest method, IMO.
This 100%. Depending on what dataset you have, I also have had success with INDEX and MATCH. Thanks to ChatGPT. If you tell it your column numbers and worksheet tab names. it will completely write the formula for you. Copy and paste. Done.
Posted on 4/27/24 at 2:50 pm to King Crab
Serious answer
You need to have a unique identifier in each of the sheets. You said inventory, so maybe a SKU
Copy and paste both data sets onto one sheet (for ease)
Utilize an xlookup and grab the sku and range the quantities.
Index and vlookup work but are now “older” tech and require formatting. X lookup is the formula of gods now.
YouTube is your friend
Oh and you need to learn excel if you want to move up and survive in industry today.
Hope this helps
You need to have a unique identifier in each of the sheets. You said inventory, so maybe a SKU
Copy and paste both data sets onto one sheet (for ease)
Utilize an xlookup and grab the sku and range the quantities.
Index and vlookup work but are now “older” tech and require formatting. X lookup is the formula of gods now.
YouTube is your friend
Oh and you need to learn excel if you want to move up and survive in industry today.
Hope this helps
This post was edited on 4/27/24 at 2:51 pm
Page 1 of 1
Popular
Back to top
Follow TigerDroppings for LSU Football News