Generate Unique List 2 Columns

Hi Guys,

I have data organized in two columns. One column contains entries that describe different causes of error. The second column contains entries showing the error type. I need to generate a string that would look like this (as an example): "There were 3 error types Three, which were caused by Administrative and Human errors". I want to be able to generate a code that tells me what unique entries for causes of error exist for each error type and I want to put that in a string. Can anyone help me, please?

Cause or Error Error Type

Administrative Two
Human Three
Technology One
Administrative Two
Technology Three
Human Two
Human One
Human One
Technology Three
Technology Three
Technology Three

Hey - thanks for the reply. I don't want to count them, I want the system to tell me (even a messagebox would work) what the causes of error were per error type. For example, looking at the extract below, I want the system to tell me "error types A were caused by Administrative, Human, and Technology causes, error types B were caused by Human causes, error types C were caused by Technology causes", etc.


Cause or Error Error Type
Administrative A
Human B
Technology C
Administrative A
Technology D
Human A
Human A
Human B
Technology A
Technology A
Technology A

It needs to be automated so that each time new data gets inputtrd into the same range, user would be able to see the that text.

If you want to use the Excel Remove Duplicates method, then here's a small dataset to test it on, just to verify that it works as advertised.

Dataset has obvious duplicates, and so quick and easy to remove these manually.

3c
3
6
1c
10
1
4c
5
4c
10
1c

So what would be the vba code to have this automated and to pop up a messagebox displaying the summary information I mentioned?

just for completeness, if you try Excel's Remove Duplicates facility (either from ribbon or via VBA) on the small dataset posted above, you may see that it doesn't always work as advertised.

appears to be somewhat buggy in fact.

Thanks, but that doesn't seem to work. Any other suggestions, please?

I can automate the data filtering to show only the unique rows but do not know how to generate the summary string after that.

another

Please Login or Register  to view this content.

Type mismatch...

type mismatch where?? (feedback not helpful otherwise)
alternative

Please Login or Register  to view this content.

Thanks - this seems to work. Is there a way to make the system automatically pick the error types?

My previous comment was in response to Sean Thomas.

I got a type mismatch on this line: n = UBound(c)

You mean something like this?

Please Login or Register  to view this content.

This works great - thanks a lot! Is there a way to generate separate text strings for each error type?

Originally Posted by kalak

another

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanks - is there a way to generate separate text strings (msgboxes) for each error type?

unsure of the present status of this thread

However regarding your
"Thanks - is there a way to generate separate text strings (msgboxes) for each error type?"

do you mean something like as given by the following code (for your opening post data)?

Please Login or Register  to view this content.

What is want is what kalak posted above but instead of having 1 text string that lists all unique combinations, i want to be able to generate separate text strings for each error type - separate message boxes for each error type. Can this be done?

Please Login or Register  to view this content.

hmm ...

consider

suppose you run the following code using your data from your opening post

for me, the code gives separate text strings for each error type on the worksheet in cells D2, D3 and D4.

it also gives a single text string in one message box

you can see, and compare, both these from the code output

are you asking for three separate message boxes, one after the other so you can click OK for each one in succession?

it's not any problem to do that (actually it is done in the code) although I don't see any purpose for it, so wonder if that is what you really do want

Please Login or Register  to view this content.

Kalak - thanks a lot for all your help. I managed to tweak the code a bit and to generate what I needed. Just one thing, out of curiosity - in the following line:

Please Login or Register  to view this content.

Is there an easy way to generate only the list after ":""? I.e., the causes of error for each error type? I saw that the join(a(i).items joins both the error type and the causes of error, but is there a way to separate the causes of error in a text string?

Thanks once again,

D.

this?

Please Login or Register  to view this content.