Find Data and create new list(s)

Hi, if I have understood you correctly, my solution should be in line with your expectations. In the 'find unique items' sheet, column A has uniques and column D has end results.

Please Login or Register  to view this content.

Regards

Maras,

This is great and does work. Thanks!

I do have one question though: Is there any way to get this to automatically update and alphabetically sort the results similar to the find uniques code originally written by Fluff13 shown below? The goal is that whenever a change is made in the strings tab that the list will update accordingly.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

This mod to the code that maras_mak supplied, should do what you want

Please Login or Register  to view this content.

@Fluff13, for example, you will make 15 changes to the 'strings' sheet in different columns - it can be in Column C, D and what?
It seems to me that it is better to assign a macro to the 'Worksheet_Activate ()' event of the 'find unique items' worksheet.
After all changes in the 'strings' sheet (columns C or D or L), you activate the 'find unique items' sheet and you have all changes - only once the macro is executed!
There would have to be small changes in your macro.
Regards.

Personally I'd just run the macro manually after all changes have been made, but the OP wanted it to be automatic whenever a change was made.
That said, I forgot to change this line

Please Login or Register  to view this content.

It should be

Please Login or Register  to view this content.

To take into account any changes in cols C:D

The results are in another sheet and after the change you can not see the results.
Changes can be seen in the activation of another worksheet!
Regards.

Agreed, but I have no idea what the sheet will be used for & the OP said in a previous thread

that it will update in real time as the data is manipulated.

So that is what I have done.
If you feel that an activate event is better, then feel free to post one. That way the OP has a choice

@ Fluff13, I do not think it's better, but practice tells me that. Hence my suggestion.

Please Login or Register  to view this content.

Regards.

Just an FYI, the spreadsheet is actually used by our electrical engineers. It is taking data from a Cable Schedule (col b = cable name, col c = cable type, col L = cable route). The idea is to first look at the route column (L) to determine all possible pathways the cable can go (these are either conduit or cable tray), and then find out which cables are in each of those segments. That is why the script shows each segment (route) with every cable that is associated with it. Those results will then be used to calculate the fill percentage of each segment (each conduit and cable tray can hold a different amount of cable and each cable type is a different size).

Guys,

I found one glitch/issue with the scripts you wrote and am wondering if its easy to fix it.

The find unique list that resides in the column A of the find unique items worksheet acts strangely. I will do my best to explain this correctly. Let's say you start with 10 unique items. The unique item list will display 10 (great). Now if you add 4 the list will update correctly and show 14 (also great). If you remove any, however, it leaves remnant information in spots which should be blank. So if you remove 2 objects from a list of 14, it will still show 14 items but the last two will just be leftovers that were not cleared. Is it hard to change the script to clear cells after the list? The data in D/E/F act correctly.

Just add this line

Please Login or Register  to view this content.

Thanks again Fluff13!

For the sake of learning...I was wondering if you could possible highlight the areas in the script that can/need to be modified if I were to aim this script at different data.

For instance, I was trying to aim this at column V instead of L. I tried changing the following red lines but it wouldn't work and I am sure I am just missing something. I changed both L's to V's and changed the 10 to 20 as that is the number of columns (I made an assumption here).

Please Login or Register  to view this content.

Your changes are correct, you just missed one.

Please Login or Register  to view this content.

This is saying if the changes was not in col L Exit. Change it to 22

And if I wanted to add another column ("U") from the Cable Routing sheet and pull it to the unique items sheet....would it look like this? Something tells me I need to change the blue line to read ReDim ar_F(1 To 40000, 1 To 4)

Please Login or Register  to view this content.

have you tried it?

Hah...I just did and my assumption was correct. It worked. Should have just had a little faith in myself. Thanks!

Glad you got it sorted.
The best way to learn, is always to try it.