Help to remove duplicate strings from table

[vangog]

Hello how to rename strings
in Excel table in the way:
"Alpha"
"Alpha"
"Bravo"
"Alpha"
"Bravo"

"Alpha"
"Bravo"
"Bravo"

"Charlee"

Should be replaced with:

"Alpha"
"Bravo"
"Alpha"
"Bravo"

"Alpha"

"Bravo"

"Charlee"

Or another example:
Apple
Apple
Apple
Orange
Apple
Apple

To this:


Apple
Orange

Apple

Purpose: to reduce multiple existance of the string (single raw message)

[Aussiebear]

Assuming your list in in Column A, maybe you could try this... If(A2<>"",If(A2<>A1,A2,""),"") in B1 and copy down

[arnelgp]

you can also try Unique() function if you are using newer excel.

[Aussiebear]

Not sure that the Unique function works here given that some values are repeated within the required range

[vangog]

I am using very old Excel. I already have a sort of complicated table. I hoped to create button with VBA macro to run on click and to remove these things. To me bore clear I have a table with columns: month, day, Q1, Q2, Q3 as for kills, and then 12 columns using IF condition and generating up to 12 messages. So these messages are formulas and the result message is like text, but actually it is result of IF function.

[Aussiebear]

From the diagram, are you wanting to use column c only as a variable unit to define what gets removed? BTW it would be much easier if we had a dummy workbook to play with rather than trying to guess a whole lot of unknowns.

[vangog]

Not C but from F to Q. But for test would be enough 1 column. File uploaded.

[p45cal]

In the attached I've only looked at column Q.
The formula in cell Q4 looks complicated, so I tried to shorten it, which I did, a bit, but it made it easier to understand. See column R which gives the same results.
Then I realised these ranges overlapped, so could simplify further: S4 formula:

=IF(AND(SUM(C4:C15)>=1350,SUM(C4:C15)<2300),W$2,"")&IF(SUM(C4:C15)<1800,X$2,"")
Same results in columns S as in columns Q and R.
Now you want to stop repeats. See if column T does it.
Is this what you were wanting?
If so you only need the formulae in column T, but note that the formula in cell T4 is different from the formulae below row 4 (T5 formula can be copied down). So copy the formulae in T4 and T5 to Q4 and Q5 and copy Q5 down. After doing that you can clear columns R:T.

[vangog]

Thank you. I see like 3 columns. Here I dont see the names of the columns because the formulae is too long, overlaping the names of the columns...
I think there is still a problem with column T.Because
I plan to have 12 columns, where are various conditions. Various limits. While there are fixed values in your formulae. So I thought VBA macro could for example copy the result text from 12 columns to memory. Just like I would copy it to notepad. And you know, I am used to Delphi, importing txt or csv files, making conditions and selection whose like I want from txt file. But I dont know how to do it with VBA. So I think it should be possible to copy it and parse it like text or am I completly wrong? Lets have a single line text, looping line by line by program, it would just remember the previous line value and compare with the current line value... And if the variables are equal, then skipping the line, not to include it to result. But Excel is very different...

[p45cal]

In the attached I've only changed column Q (the same as colum T in my last workbook).
First, is column Q showing what you hoped for? (I ask because I'm still not sure what you want.)

While there are fixed values in your formulae.Your formulae had fixed values! I had nothing else to go on! I used your fixed values.



So I thought VBA macro could for example copy the result text from 12 columns…Yes it can, and clear content from lines you want cleared, but be aware this will replace any formula with plain text. Those formulae will be lost, so if data on which those formulae used changes, the plain text will not change/update.


Maybe you want the results in an entirely different place (another sheet?)?

I'm still very, very unclear about what you want.

[Aussiebear]

@vangog. You talk in ambiguities yet you require a specific result. Seriously, what is it that you want? Your initial request was to remove duplicate strings based on potentially a single column, yet now it's far more complicated that initially indicated. Please stop for a minute and think about what it is that you are requiring? You need to remember that we are complete outsiders to your project. There fore when you make a request for assistance you need to provide enough information to ensure we understand what it is that you intend to complete from an outsiders point of view..

[vangog]

Thank you for your effort, but it will be faster when I will do it with Delphi instead of trying VBA. I just will copy the results as strings and I will loop & parse them with program to skip duplicates. I have no more time to write here. Thank you. I'm Finished.

[arnelgp]

:hi: