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
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
Pete
Struggling Solutions Architect
=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
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
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
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.
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Hi Thilag,
is this the sort of thing you are looking for?
Use the follwoing array formula entered with Ctrl Shift Enter
removed repeated charecteristics with 1 creteria V2.xlsx
Excel Guru in the making
<----------If the question has been answered to your satisfication please click the Add Repuation star to the left
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
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
CMan, drag column C down one more line, and it returns an error at my end
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
Let's wait for Thilag to respond, we have thrown so many options to him and he's not watching at the moment
Yeah best to wait
Hello Friends
thanks for all of your reply.
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
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
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
Please see the attached and let me know if this works for you
removed repeated charecteristics with 1 creteria V2.xlsx
TheCman81 file helped me
thanks
thilag
No problem, we got there in the end
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks