Find every unique item, create a list of uniques, and count every instance of each type of item VBA Excel

[desert_dweller5]

My friend is doing a huge inventory for work. He has thousands of items and an unknown number of duplicates. He wants to create an index sheet with all the different items and counts for each type of item. He wants Excel to "auto-magically" figure out how many different types of items there are, what they are, list them in column A and then count each instance in the workbook or workbooks of each item in column B. I'm also assuming that he wants this to update every time he edits his workbook(s) to have an accurate count in real time. He doesn't know how many items or types of items he has but it's in the thousands possibly tens of thousands of items.

is something like this even possible? where do I even begin? I'm pretty savvy with Excel but I'm not a programmer. My friend is a basic user who can enter data and format, that's it. so it has to be automatic.

any suggestions?
Thanks in advance
Desert_dweller5

 

[desert_dweller5]

Dear WarPigl3t: Thanks for continuing to reply. to answer your questions...
1. I don't think that would be a problem The workbooks can be in the same folder as far as I know.
2. I don't know, if I had to guess probably not.

Let me explain here: What I mean that the ranges are different is that the names are not in 1 column or 1 row it would have to be customized to each individual worksheet (as far as I know), to have it look at each range individually, because there is extraneous data on each sheet. so if there are instances of the search term that are not items but are false positives, then those would have to be deleted manually defeating the purpose of the macro, to be something that doesn't need to be edited by hand, since there are so many.
3. No there isn't a header row. nor a header column.
4. These are set up like a visual representation of the actual thing from 3 dimensional space in 2 dimensional space on excel, so it's not set up like a "normal" or traditional spreadsheet where you have one column or row per type of thing, for example column A is "item name". Nor is it set horizontally, where row 1 is "item name." It might be in row 1 or it might be in row 32 or row 3. it varies. The layout is the challenge to get around. Unfortunately I can't show you.

This is why it's so challenging because of the layout. I will explain that there are certain rows with the type of item in it. but you can't rely that the will be all the same row numbers across worksheets let alone workbooks. This is why i say the ranges are all different. where one is looking is never the same. Because of the false positive problem I'm not sure if it's possible to simply search the whole page. plus we don't know what we are looking for. I'm not sure if any of this matters or not. please advise.

 

[WarPigl3t]

I see. Well its not impossible, just tedious. I have the ability to do it, but I am sorry that I won't. The amount of code that will be required is so much that I'm not willing to devote that much time in it. I estimate it would take me 2 hours to code this. I just do this forum thing as a hobby to keep my programming skills up. You should hire a programmer. I recommend www.Freelancer.com and create a Visual Basic project. Put the Bid at $50 or more.