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

 

[sheetspread]

the pivot table does this

 

[WarPigl3t]

Sub myMacro()
     'DEFINE THE VATIABLES BELOW BEFORE RUNNING THE MACRO!
     dataWorksheet = "Sheet1" 'The worksheet name of the worksheet with the tens of thousands of items you are talking about.
     indexWorksheet = "Index" 'The worksheet name of the index worksheet you are talking about.
     itemsColumn = "A" 'The column letter of the column that contains the item names in your inventory worksheet.
     firstRow = 2 'firstRow = 1 if you don't have headers.
     
     'DO NOT MODIFY BELOW THIS LINE!
     lastRow = Sheets(dataWorksheet).Range(itemsColumn & Rows.Count).End(xlUp).Row
     uniqueItemsList = uniqueItemsList_Function(dataWorksheet, itemsColumn, firstRow, lastRow)
     Sheets(indexWorksheet).Cells.ClearContents
     Call populateIndex_Subroutine(dataWorksheet, indexWorksheet, itemsColumn, firstRow, lastRow, uniqueItemsList)
End Sub

Function uniqueItemsList_Function(sheetName, c, r, lr)
     Dim myArray() As String
     a = 0
     ReDim Preserve myArray(a)
     Do Until r > lr
          myValue = Sheets(sheetName).Range(c & r).Value
          addToArray = True
          For Each element In myArray
               If element = myValue Then
                    addToArray = False
               End If
          Next element
          If addToArray = True Then
               ReDim Preserve myArray(a)
               myArray(a) = myValue
               a = a + 1
          End If
          r = r + 1
     Loop
     uniqueItemsList_Function = myArray()
End Function

Sub populateIndex_Subroutine(inputSheet, outputSheet, c, firstRow, lr, myArray)
     p = 1
     For Each element In myArray
          r = firstRow
          myCounter = 0
          Do Until r > lr
               myValue = Sheets(inputSheet).Range(c & r).Value
               If element = myValue Then
                    myCounter = myCounter + 1
               End If
               r = r + 1
          Loop
          Sheets(outputSheet).Range("A" & p).Value = element
          Sheets(outputSheet).Range("B" & p).Value = myCounter
          p = p + 1
     Next element
End Sub

 

[WarPigl3t]

This macro does not run automatically. I would advise against running this automatically because it will be slow each time you do anything. But if you really want to run this automatically, add this code to your worksheet code from your inventory worksheet.

Private Sub Worksheet_Change(ByVal Target as Range) 
    Call myMacro
End Sub

 

[desert_dweller5]

Thanks to sheetspread and WarPigl3t for your responses.
@sheetspread I will definitely check into pivot tables. I'm not sure that will fit the need specifically enough though.
@WarPigl3t thanks so much for writing all that code for me. I have a concern though. The data are not on one worksheet. They are spread out across multiple sheets and possibly could be spread over multiple workbooks too. So the main concern is first collocating all this information from all the sheets and/or other workbooks onto one worksheet then having a count of each instance of each item. but again as little user intervention as possible. I was looking at Chip Pearson's website.

FindAll XLA Add-In

He has an add in that is a find all and that creates a list of all the instances based on a search term but that's not exactly what I'm looking for because it pulls in undesirable data into the list. I wish I could be more specific. I don't have much detail to go on. it's an inventory of phone equipment and he has all the data in many different horizontally aligned tables that matches the layout of the phone system. The dimension and layouts are all completely different so ranges are not the same. the other complication is there might be extraneous information in the same cell with the model number we're looking for. it's not a huge list that just be sorted and analyzed. That's what I need is a huge list so I can pick out the different types of system components. Then be able to count each instance of each type. I hope this makes more sense.

sorry for the long post.
Desert_dwelller5

 

[WarPigl3t]

You should hire a programmer. That's a lot of work.

 

[desert_dweller5]

Yep. That's what I thought. Thanks WarPigl3t. unfortunately the budget is zero. I'm just a hobbyist excel user and he thinks I know how to get him to the moon. and i told him I have no clue. I feel bad that i can't do it but it's beyond my expertise. Plus I'd rather not work for free. :D Thanks again.
Desert_dweller5

 

[sheetspread]

From what you both have said this does sound like a massive cleanup project with data scattered randomly in many places. In case there is some common structure to it all (unlikely), you can merge together then just get the unique item counts through traditional methods.

 

[desert_dweller5]

it's not randomly scattered per se. it makes sense when you look at the spreadsheets what the layout of the cards is. but as far as it easily being able to be analyzed no. The ranges are all different shapes and sizes. The desired information is in every other or every Nth row depending on the sheet or workbook. I'm not sure because I've only seen pieces of it. and don't have a copy. so it's nearly impossible to tell what the exact make up of it is. so... I think he's on his own. he wants the computer to figure it all out for him with nearly zero input from him. He tends to set up spreadsheets in a non-traditional way. instead of just rows and columns to display the info he has to lay it out so that the spreadsheet visually matches the layout of what he's inventorying. which is helpful for field technicians but not easily analyzed by the data geeks like us. it's just frustrating. if i had a master list this would be simple but with out one it's next to impossible because of the layout. KISS is definitely not his motto. it has to be the way he needs it to be and I have to try to work around that. just frustrated. Thanks for listening to me complain.
Desert_dweller5

 

[WarPigl3t]

I reread your post and it might be possible to do with the code I already provided with an additional piece of code I might write for you. But first I need to ask you some questions to see if it is possible.

1. So first let's talk about all the different workbooks that have data you need to count. The macro I create would need to have all the workbooks in the same folder as the active workbook. The active workbook being the workbook were you want the output to go and were this code will go. Is that going to be a problem?

2. You mentioned that data ranges are not the same. From what I can tell, it doesn't matter. The only thing we are looking at is one specific column, the item names column. Is the item names column the same column letter in all workbooks?

3. If the item names column is not the same column letter in all workbooks(If you answered "No" in the number 2 above question), is the header row the same in all workbooks? Most people use row 1 as the header row. Is row 1 the header row in every workbook?

4. If the header row is the same for all workbooks(If you answered "Yes" in question 3 above), is the items column header value the same in all workbooks? For example, in one workbook you may have the header of the item names in cell A1 and you might call it "Item Names". Is it also called "Item Names" in every workbook?

Note: If I make this code for you, it would be extremely slow and you should most definitely not run it with a work sheet on change event. In other words, not automatic. You should apply the code to a button instead.