Finding duplicates in 6 columns by excel VBA

[essasmj]

Dear arnelgp
thanks a lot, I think it's working perfect
highly appreciated

can you teach me more about the logic you used
also for this "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=", will it work in any computer with excel

[arnelgp]

it will work on Excel 2007 and newer.
you can google the "Excel connection string" on the net.

[essasmj]

Great work arnelgp
I am trying to get the learn the logic of "Excel connection string"

I have two questions regarding the code
if I want to increase the number of columns in the search from 6 to 10 what to change in the code
if I want to ignore the alphabitical sorting, what to comment or remove from the code

[p45cal]

Here's one to test, it may not work depending on your version of Excel.
Results look like this:
2023-02-11_170826.png
In the listbox you can see a number in brackets after each item, this is the number of columns it appears in (see later for more on this).

There's a new cluster of controls:
2023-02-11_171129.png

If you click on the spinner control it'll change what's shown in the listbox:
2023-02-11_171009.jpg

They are sorted in descending order.
Is this something that's worth proceeding with? (The code etc. is very scrappy at the moment.)

Note, that earlier I mentioned about the number in brackets after each item; with your data as it is in the Poison sheet there are duplicate items in several columns, they're highlighted on that sheet with conditional formatting. These are causing medically misleading results. For example, if we select only Bradychardia it shouldn't have more than a 1 in the brackets (because we're only looking at one column) but it has a 2. You need to remove these duplicates. I only noticed this later and haven't bothered to correct it in the code, but it should give correct results if you remove the duplicates.
2023-02-11_170152.png

[essasmj]

thanks a lot p45cal for your help
Regarding the duplicates inside the columns I worked in them, and they are gone in my version
"bs =" this is making an error " Can't find project or library, I can't proceed after that. also I don't want to start guessing and miss your code

Regarding the spinner and the descending order ( sorry, I couldn't test the details because of the above error), I think this will be confusing to the user, the user wants to select from comboboxes and see the result in listbox. keep in mind that users are not Excel or VBA experts. it will take a lot of teaching to get this logic
I think the easiest for them is just to show duplicate values based on different values in comboboxes without spinner

[p45cal]

"bs =" this is making an error " Can't find project or library, I can't proceed after that. also I don't want to start guessing and miss your code

No version of Excel should have a problem with that line. Did you try the attached file without making any changes?
What version of Excel are you using?

Regarding the spinner and the descending order ( sorry, I couldn't test the details because of the above error), I think this will be confusing to the user, the user wants to select from comboboxes and see the result in listbox. keep in mind that users are not Excel or VBA experts. it will take a lot of teaching to get this logic
I think the easiest for them is just to show duplicate values based on different values in comboboxes without spinner

OK I'll leave it there. The attached is the same file with the duplicates removed.

[arnelgp]

if you are going to use 10 combobox (or 10 selections)
i already created some variables (with comment) on the Click event code of the button.
you just remove the comment if you already set it up as 10 combobox.

i remove the sorting.
if you want it back just look at the code 'Sorting array from A to Z.
and uncomment For...Next loop.

[essasmj]

thanks p45calfor your for the efforts

still receiving this error "bs =" this is making an error "

my office is 2021
I could not go further since I don't know how to bypass this error
I tried to dim bs as array but the same error came with this line

For Each b In bs

[essasmj]

Thanks a lot arnelgp , great work
I will test it those days, since I may need to increase the number of comboboxes

[Aussiebear]

Oh here we go.... The initial request was bogus, the initial terms a deflection.

[essasmj]

why
what was bogus in my request. I didn't get it ?

[Aussiebear]

Initially you stated "I have this code to check if a text is duplicated 6 times in 6 columns'. Then went on to say later "if I want to increase the number of columns in the search from 6 to 10 what to change in the code"
Now it seems that you are expanding to 10 combobox selections. For 10 comboboxes, one assumes, given the context of your sample, a minimum of 21 columns of base data. These are things you failed to mention earlier.

You also stated "Each combobox will allow the user to select a different column in the sheet (sheet5), except the selection “Not Sure”. It will go to column “O”, which contain all the texts in other columns (without duplication)." The value "Not Sure", is one of three possible values for columns 1 to 4 and one of five for column 5, was set as the default value for all 5 columns. You run the possibility of corrupting the output before you even start. A person might mistakingly skip over a test because of the set value.

You stated "Once the columns are identified, the code will check, if inside those 6 columns there is a common repeated text (must be repeated in 6 columns, not in 5 or less). Yet later on you say "I think the easiest for them is just to show duplicate values based on different values in comboboxes without spinner". The fact that there must be a common repeated text in all 6 columns, for that text to be listed, refutes your last statement.

[essasmj]

Dear Aussiebearsdg thanks for your time in replying to me

it looks like the explanation was confusing to you. I will try to explain as much I can based on my point and my english. but before that thanks again for your time
For me nothing is bogus or goofy
To start with I am not spending all this time in this project for profit. its a gift for my neighborhood hospital

regarding your first point
I was struggling with 6 columns. But when "arnelgp" sorted in out, I started thinking why not to do it for more columns since I received a longer list from the hospital about "poisons" (around 40 columns, needing at least 15 comboboxes). but my skills in excel could not help me to do that so I asked the hospital about the most important ones and I selected 6 (13 columns)
Based on my VBA skills (array, dictionaries and CountA) I could not solve it for 6 columns this is why I asked for help from the forum

regarding your second point
the value "Not Sure" was set as default for all comboboxes because the patient may not have that finding (the patient may come with no "mydriasis" and no "miosis" so in combobox10 the choice will be in this case "Not Sure")
Because I ended up using CountA after I couldn't solve it by Array or Dictionary, I copied all the texts in the columns from "A" to "N" to the column "O" and removed any duplicate inside "O" to satisfy the countA logic which I was building


regarding your last point
one sentence is talking about the code the other is talking about the user

this sentence "Once the columns are identified, the code will check, if inside those 6 columns there is a common repeated text (must be repeated in 6 columns, not in 5 or less) is talking about the code not the user, and it's till now still valid. Even with "arnelgp" solution the code will give only the values if they are repeated in the 6 columns. if the value repeated in 5 columns it will not appear in listbox1


the sentence "I think the easiest for them is just to show duplicate values based on different values in comboboxes without spinner" is talking about the users not the code The users will not see the code. they will only see what's coming in listbox1. I wrote this sentence as an answer to "p45cal" because his code was getting the values repeated "duplicated" in listbox1 with a number indicating how many times they are duplicated. this will not help physicians or nurses
in medical field if the patient came with "Miosis" and "Tachycardia" and "Hypotension" and poisoning is suspected, the physicians want to know which poison can caused those three ( "Miosis" and "Tachycardia" and "Hypotension") together which will appear to them in listbox1
Physicians will not care how many times the value is duplicated inside the excel sheet, so showing the number beside any value in listbox1 will not help them


I hope I explained myself well in this time
my apology for any confusion

[p45cal]

Physicians will not care how many times the value is duplicated inside the excel sheet, so showing the number beside any value in listbox1 will not help them

This is not showing the number of times the poison is duplicated in the sheet, it's showing how many times the poison appears in only the columns chosen by the user. As long as there are no duplicates (as there were) within any single column that equates to the number of columns the user has chosen that the poison is found in.
If you choose only to show poisons in the listbox that are present in all the columns that the user has chosen, then the list wouldn't show a poison if only 4 out of 5 columns had that poison. I would have thought that a clinician would be very interested to see that sort of result.
Imagine this scenario: A junior accident and emergency doctor, who's been on a hectic shift all night, and had 'enjoyed' himself at a party the night before that, may have decided the patient had miosis, not realising in his tiredness that he had been examining the patient's eyes in the full glare of the morning sun beaming in through his office windows. He includes miosis as a column to search. He gets no results. He does nothing. The patient dies. Had he been able to see results which didn't include miosis he might have seen poisons that were present in all the other columns, find the antidote and the patient lived. Hurrah.
I'd have thought that was helpful to the clinician.
The number in brackets shows the number of columns each poison was found in, and the spinner (defaults to the top score only) allows them to see only the top scoring count of columns, or more lower scores, should they want to.

[essasmj]

Thanks p45cal for your clarification
first I am not underestimating your efforts. you did a great work with very beautiful logic.

let me explain those two points
I think you gave a great example. but I worked in what was requested from me by the hospital (this is why I mentioned "physicians will not care......."). the request was to display the poisons that can cause all the signs mentioned in comboboxes together. I will discuss your logic with the physicians. may be it will be better if we can show in which column the poison appeared from the user selection to be more informative

regarding "This is not showing the number of times the poison is duplicated in the sheet, it's showing how many times the poison appears in only the columns chosen by the user"
my apology I didn't get it when you shared the file since till now I couldn't bypass the error

[p45cal]

I will discuss your logic with the physicians. may be it will be better if we can show in which column the poison appeared from the user selection to be more informative

Let's wait and see what they say.

A test, could you put this formula in cell Q2 of the Poison sheet of the file I last attached:
=Poisons(TOCOL($A$2:$K$150),6)

1. Do you get this?:

2023-02-14_120003.png

since till now I couldn't bypass the error

2. Are you saying you've now resolved this error?
If not:
3. Is anything highlighted in yellow while this error is thrown (or after you dismiss the message)?
4. Did you try the file I attached 'right out of the box' without changing anything in it?


Separately,
5. What version of Excel are the physicians using?

[p45cal]

If anyone else tried my file in msg#26, did you also get the compile error?

[georgiboy]

If anyone else tried my file in msg#26, did you also get the compile error?

Jst tried it and i seem to get what you have pictured in Pic 1 above - did not get any errors.

[p45cal]

Jst tried it and i seem to get what you have pictured in Pic 1 above - did not get any errors.

Excellent! Thanks. Did you mean Pic 1 in msg#36 or msg#24?
If msg#36, did the userform work too?

[georgiboy]

Excellent! Thanks. Did you mean Pic 1 in msg#36 or msg#24?
If msg#36, did the userform work too?

Pic 1 from post 36

It was the userform i was testing, i selected something for each combobox and clicked 'Show the suspected Poisoning' gave me the below
Untitled.jpg

Well in the above i left 2 boxes as 'Not Sure' but this was a second run so you get the idea.