# removed repeated charecteristics with 1 creteria

1. ## removed repeated charecteristics with 1 creteria

Hello Friends

Please find the attached file : <removed repeated charecteristics with 1 creteria.xlsx>

I want to remove the repeated charecteristics and it is to be plotted once also `XXX' should be removed and should not present anywhere.

thanks

thilag

2. ## Re: removed repeated charecteristics with 1 creteria

Not sure thilag...

In cell C1 you have blank, which fits the criteria that XXX should be removed if it appears in cell A1.
Then in cell C3 you have BBB - do you want a rolling increment every time AAA occurs in Column A?
And going back to the XXX scenario, should not cell C5 and C6 be blank, as the XXX reference is removed????

Also, using letters as you are, you will soon run out of variables:
what happens when you get to WWW - do you insert XXX????
what happens when you get to ZZZ???

cheers
Pete

3. ## Re: removed repeated charecteristics with 1 creteria

=IFERROR(INDEX(A\$1:A\$250,MATCH(0,INDEX(COUNTIF(C\$1:C1,A\$1:A\$250&"")+INDEX(A1:A250="XXX",0),0),0)),"")
Try this in Cell "C2" and copy towards down

4. ## Re: removed repeated charecteristics with 1 creteria

Hello dubcap01

Sorry my file and illustrations not sufficient for your understanding.

You can start with Cell C1 (I just made example in C1). My Requirement 1) is XXX should not present anywhere 2) other than XXX the given charecteristics should be visible only one time, There is no rolling increment neede, my intention is i want to say anywhere (between the cells in Column A) XXX may present.

AAA
BBB
CCC
DDD
EEE

should be plotted by omitting the XXX and the repeated things.

Hope i clarified you.

thanks

thilag

5. ## Re: removed repeated charecteristics with 1 creteria

In C1 Cell

xxx

In C2 Cell - Array Formula - Requires CTRL+SHIFT+ENTER

=IFERROR(INDEX(\$A\$1:\$A\$25,MATCH(0,COUNTIF(\$C\$1:C1,\$A\$1:\$A\$25),0)),"")

Drag it down...

Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

6. ## Re: removed repeated charecteristics with 1 creteria

Hi Thilag,

is this the sort of thing you are looking for?

7. ## Re: removed repeated charecteristics with 1 creteria

Use the follwoing array formula entered with Ctrl Shift Enter

removed repeated charecteristics with 1 creteria V2.xlsx

8. ## Re: removed repeated charecteristics with 1 creteria

Thilag, everyone else

As I explained earlier, surely there are going to be limits to using array formula as this will inevitably hit XXX, and what happens then?
And no-one hs progressed past the five entries Thilag had in Column C...

Pete

9. ## Re: removed repeated charecteristics with 1 creteria

You need to explain yourself more, what I got is you are looking to return unique values excluding XXX, if that't the case then my formula will work

10. ## Re: removed repeated charecteristics with 1 creteria

CMan, drag column C down one more line, and it returns an error at my end

11. ## Re: removed repeated charecteristics with 1 creteria

That's because there's no more unique values in your list, in your list you only have 5 unique values

If this is not what you want then you need to giove a more detailed example

12. ## Re: removed repeated charecteristics with 1 creteria

Let's wait for Thilag to respond, we have thrown so many options to him and he's not watching at the moment

13. ## Re: removed repeated charecteristics with 1 creteria

Yeah best to wait

14. ## Re: removed repeated charecteristics with 1 creteria

Hello Friends

I am not looking for sort,
Sixthsense formula helps me, thanks
TheCman81 formula also works fine

But if we add the FFF in the Cell A26 and copy the formula by Ctrl+D (C6 to C7, before we should edit A\$25 to say A\$250)
then Sixthsense formula working, but other formula not works

i.e.,
In Cell A26 = FFF, C1 = XXX
then C7 = <=IFERROR(INDEX(\$A\$1:\$A\$250,MATCH(0,COUNTIF(\$C\$1:C6,\$A\$1:\$A\$250),0)),"")> array formula THEN IT SHOWS FFF.

Thanks to all

thilag

all good bud

16. ## Re: removed repeated charecteristics with 1 creteria

Also

Without entering XXX in Cell C1, when we are adding the texts in the cells below cell A25 (For example A26= FFF) then the formula should display FFF in the C7 is great.

I.e., adding text below A25 has to leads to plotting these values / texts by eliminating the repeated occurance is required.

thanks

thilag

17. ## Re: removed repeated charecteristics with 1 creteria

Also

My actual requirement is

We should not enter XXX in Cell C1, then if we add any texts in column A bottom (After Cell A25, if we add FFF) then it should be displayed in Cell C7 automatically. The formula should be universal not local (A:A instead \$A\$1:\$A\$25) also If we drag (or Ctrl+D) then it should show next alphabet.

thanks

thilag

18. ## Re: removed repeated charecteristics with 1 creteria

Please see the attached and let me know if this works for you

removed repeated charecteristics with 1 creteria V2.xlsx

19. ## Re: removed repeated charecteristics with 1 creteria

TheCman81 file helped me

thanks

thilag

20. ## Re: removed repeated charecteristics with 1 creteria

No problem, we got there in the end

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1