Weeding out Duplicate numbers

[dwrowe001]

Hello,

As I stated in my previous post, https://chandoo.org/forum/threads/identifying-number-strings-from-target-numbers.54208/ , I had a multi-stage question I needed help with.

I posted my first question with a attached example. Vletm helped me with this first post which is working out perfectly.. Sheet 1 of the attached file in this post shows how it works. Place the number you want to shearch on (0-9) in cell O2 and hit enter.

For this new request, I would like to filter out and keep only the duplicate number strings from the results that vletm’s solution provides, B14 : K?? Last line of the returned results varies.

Duplicates should be listed next to the returned results from vletm’s script, starting in cell N14 with the number 0, meaning all duplicates starting with zero and how many of that number are found, in parentheses, ie.. 070 (3). If there is only 1 instance of a number then it shouldn’t be listed.

I would like a separate VBA script for this problem, and running of the Duplicates script should be initiated by clicking a button so that I can run it after running vletm’s script. Sheet 2 shows additions and how I would like the duplicates placed.

I know your time is valuable, so I do greatly appreciate any and all help offered,.. Thank you!!

Dave

 

[vletm]

dwrowe001​

I would be smoother for everyone, if You could give all needed details at once.
Many times even 'minor' changed or new details could cause ... do it from zero.
I'm offering this version - apply number to cell O2.
You can still see - what is going on.
There are reasons - why my layout is as it is.
I'm using my file - You can change those colors again as You would like to have.

 

[dwrowe001]

dwrowe001​

I would be smoother for everyone, if You could give all needed details at once.
Many times even 'minor' changed or new details could cause ... do it from zero.
I'm offering this version - apply number to cell O2.
You can still see - what is going on.
There are reasons - why my layout is as it is.
I'm using my file - You can change those colors again as You would like to have.

Understood, I only did the posts this way because originally I was wanting to try to learn how to do it myself... I thought if I broke it up I might be able to figure some of the VBA out. But as it is now, I look at the code and shake my head in discouragement... Thanks for your input and help.

Dave

 

[dwrowe001]

vletm,

After you submitted your updated file isolating the duplicate numbers I began to notice the sheer number of duplicates… and after a some investigating I discovered what is possibly the reason. Let me try to explain using the two 5’s in Q3 and Q4



In the above pasted snapshot, for the targeted number 5 in Q3 and Q4 the number strings returned are:



The issue I found is, when returning the number strings, none of them should include the focused target number they are surrounding..
Examples provided below:

The 5 in Q3 is the focused target for numbers 1,0,5,4,4, surrounding it. The highlighted numbers should not be returned since they include
their focus number:

The cell highlighted in red I think is going outside the search group range and picking up
The 5 in cell O2..

The 5 in Q4 is the focused target for numbers 1,0,7,4,6,4,4,5 surrounding it. The highlighted numbers should not be returned since they include their focus number:


I hope this makes sense? This is getting complicated, I’m not sure if its fixable?? Can you figure this out easily or all?

One other thing which is minor and not problem, just a preference. would you be able to move the Duplicates to the left a couple columns as shown below?


Thank you,
Dave

 

[vletm]

Is below same like Your above the 1st case?
025 to left ... isn't there 5 or ... ?


Before You'll verify above case - which You've asked to do that way ... or not?

Your minor and not problem, just a preference.
Could solve like below ..
Left snapshot - BEFORE ... and ... Right snapshot - AFTER minor Your 'modification'
.

... hmm?
You're still thinking ...
I did 2nd sample.
If cell A1 is empty then less data
otherwise ... as in my previous sample.

Could You explain - what do You mean while You wrote ... easily?
... anyway - I didn't use yellow colors in those cells or how?
It's more clear to show/explain - what do You want? ... than something else.

 

[dwrowe001]

Is below same like Your above the 1st case?
025 to left ... isn't there 5 or ... ?
View attachment 84746

Before You'll verify above case - which You've asked to do that way ... or not?

Your minor and not problem, just a preference.
Could solve like below ..
Left snapshot - BEFORE ... and ... Right snapshot - AFTER minor Your 'modification'
View attachment 84747. View attachment 84748

... hmm?
You're still thinking ...
I did 2nd sample.
If cell A1 is empty then less data
otherwise ... as in my previous sample.

Could You explain - what do You mean while You wrote ... easily?
... anyway - I didn't use yellow colors in those cells or how?
It's more clear to show/explain - what do You want? ... than something else.

in the below snapshot, the hightlighted 5 is the focused target number for the surrounding numbers
0 9 1 4 3 in red. the 3 number strings coming off of that zero are outlined with the green arrows. The thick red
arrow shows that the 3 number string 053 cannot be used because it includes the target #5.



below is another example:



this should be procedure for all target numbers and their ring of surrounding numbers.

with regard to my statement about being able to easily fix the issues... this was merely an expression.. I know all this is time consuming, and
not easy.. well, maybe it's easy for you, I don't know... Again just an expression and not intended to cause issues or animosity.

 

[vletm]

# Did You answered to my the 1st question?
... do those 205 & 025 include 'other's 5' too?

or did You explains something else?

# easily
Or do You mean possible?
Many thing would be possible and more possible, if could get answers.

# Did my the latest sample give expected results?

 

[dwrowe001]

# Did You answered to my the 1st question?
... do those 205 & 025 include 'other's 5' too?
View attachment 84755
or did You explains something else?

# easily
Or do You mean possible?
Many thing would be possible and more possible, if could get answers.

# Did my the latest sample give expected results?

Almost there… with regard to the focused 5 (Circled red) in cell Q3,
The numbers in squares are the surrounding numbers for the focused 5 at Q3.



your lastest file (dwrrowe001) you returned listed below:

two strings were not returned for the 5 in Q3.
The 5 in Cell Q3 is the focus… the numbers surrounding Q3 are in red squares: 1 0 5 4 4…
The 5 in Cell Q4 is one of those surrounding numbers and should be used when returning numbers strings.



Same with the other numbers surrounding the focused target 5 in Q3..



I’m sorry for the confusion.. I’m trying to explain the best I can.. hence the circles and squares now. .lol

I hope this clears it up…
Thank you for your patience with me!!

Dave

 

[vletm]

You answered to my the 1st question ... Almost there
... should that help me?

My code tries to solve those values in specific order ...

Instead that - You jumped to different cell

You gave to different ... snapshots from same range
Try to focus 2nd line 042 - right side cell is empty

... and in Your 2nd snapshot - that empty cell has value 054

if those presents ... what are Your expected results then ... hmm?
I've a challenge ... I could get same range ... but it looks like below

For me, those (Yours and mine) 'results' are different.
OR
in that my asked range ... which was Almost there.
For me Almost there is like 1 + 2 = 4 ... it should be 3.

 

[dwrowe001]

You answered to my the 1st question ... Almost there
... should that help me?

My code tries to solve those values in specific order ...

Instead that - You jumped to different cell

You gave to different ... snapshots from same range
Try to focus 2nd line 042 - right side cell is empty
View attachment 84760
... and in Your 2nd snapshot - that empty cell has value 054
View attachment 84761
if those presents ... what are Your expected results then ... hmm?
I've a challenge ... I could get same range ... but it looks like below
View attachment 84762
For me, those (Yours and mine) 'results' are different.
OR
in that my asked range ... which was Almost there.
For me Almost there is like 1 + 2 = 4 ... it should be 3.

Regretfully I did provide conflicting snapshots.. sorry for that.
the below snapshot shows desired results..



if this is what you have for corrected results:

it looks good...

Dave.

 

[vletm]

dwrowe001​

For me Almost there is like 1 + 2 = 4 ... it should be 3.
This looks something else than good for me.
You wrote something about ... corrected results
... where? ... in Culpeper?
Take care.

 

[dwrowe001]

Thank you....

dwrowe001​

For me Almost there is like 1 + 2 = 4 ... it should be 3.
This looks something else than good for me.
You wrote something about ... corrected results
... where? ... in Culpeper?
Take care.

 

[vletm]

dwrowe001​

You seems to modify my writing above ...
Check wide yellow text...

 

[dwrowe001]

ok, tks