Hello,
If you have to separate out;
Items beginning P with C & D but do not count items beginning with either C or D
In the row format you can manually select each P but it trails into well over 500…
Rows doesn’t do much either…
Can anyone excell wizard advise is there a formula for ruling out items beginning with C or D from being counted against total Inventory.
It’s stumping me !
Thanking you in advance.
you can write a macro in the VBA coding environment in Excel. the command left(string1,n) will extract the first n characters from a string that you feed it. If you omit n, it is a assumed to be one. Now write a simple macro using that function and have it check each item in the column or row for the first letter, and then add +1 to a count variable each time it finds one beginning with p. Then print that count variable to whatever cell you feel like.
Sorry, I am a bit pressed for time, or else I would write a sample program. That should get you started though. Good luck!
Double post, sorry.
You could also do this:MS Excel 2011 for Mac: Sort data in alphabetical order based on 1 column
order the column/row in alphabetical order, and then it is easy to determine how many cells begin with P from their locations.
If I understand correctly, you’re looking to do something similar to the attached image.
If so → You can make the formula for cell C2:
=IF(OR(LEFT(A2) = “C”, LEFT(A2) = “D” ), 0, B2)
Click & drag the formula apply it to all the cells in the same column & you’re all set.
How do you just find out the amount of items beginning with P and the amount of stock.
Imagine 2,000 per C,D and P so you couldn’t just count via excel.
You accidentally bought a bootleg copy, kinda like going to the store for Fruit Loops and leaving with the supermarket brand ‘Colored Citrus Os’. And now you’re surprised when you get GI lacerations from the drywall used to give it extra crunch.
Buy ‘Excel’ and you should find things easier.
[quote]anonym wrote:
You accidentally bought a bootleg copy, kinda like going to the store for Fruit Loops and leaving with the supermarket brand ‘Colored Citrus Os’. And now you’re surprised when you get GI lacerations from the drywall used to give it extra crunch.
Buy ‘Excel’ and you should find things easier.[/quote]
Eh mods changed the title I think … I do have excel.
It’s for work so I doubt it’s bootleg…
Cheers
how many times do you have to do this task?
A quick and dirty way would be sorting the column alphabetically, then summing the associated numbers in the next column. I’m sure there is a better way tho.
[quote]Captnoblivious wrote:
how many times do you have to do this task?
A quick and dirty way would be sorting the column alphabetically, then summing the associated numbers in the next column. I’m sure there is a better way tho.[/quote]
It’s an example of the kind of stuff they would be requiring me to do if I applied for that particular department…
Alot would be a simple answer to your question.
I cannot figure out how to rule certain letters
Don’t know if this is what you’re looking for, but I did a quick formula in one cell that counted the number of instances in a range of cells (a column) that a value starting with “P” (ie. “P*” occurred.
I made a quick table based on your previous post.
Hope this helps.
Should give you a starting point anyway.
It scales fine. The yellow cells are the ones I’d expect it to find.
Thank you very much Steely.
[quote]Voluminous wrote:
Thank you very much Steely.
[/quote]
No prob— was that what you were looking for?
[quote]SteelyD wrote:
Don’t know if this is what you’re looking for, but I did a quick formula in one cell that counted the number of instances in a range of cells (a column) that a value starting with “P” (ie. “P*” occurred.
I made a quick table based on your previous post.
Hope this helps.
Should give you a starting point anyway.[/quote]
Is that Windows 98?
[quote]tedro wrote:
[quote]SteelyD wrote:
Don’t know if this is what you’re looking for, but I did a quick formula in one cell that counted the number of instances in a range of cells (a column) that a value starting with “P” (ie. “P*” occurred.
I made a quick table based on your previous post.
Hope this helps.
Should give you a starting point anyway.[/quote]
Is that Windows 98?[/quote]
No.
I still don’t think that’s quite right SteelyD → If you look at his screenshot, the “D” column has a count of how many items for that particular “C” column exist. That means that he needs to sum the values of the “D” column only when “C” column match the criteria of starting with a “C”, “D”, or “P”.
I’m attaching another screen shot using the formula I showed before. (Modified slightly to include the “P” column.
I’ve basically added a 5th column that will contain a number greater than zero only if the “C” column starts with a letter “C”, “D”, or “P”.
