+ Reply to Thread
Results 1 to 20 of 20

removed repeated charecteristics with 1 creteria

  1. #1
    Forum Contributor
    Join Date
    02-25-2012
    Location
    Coimbatore, India
    MS-Off Ver
    Excel 2010
    Posts
    445

    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. #2
    Forum Contributor
    Join Date
    06-23-2014
    Location
    Dubbo, Australia
    MS-Off Ver
    EXCEL 2019
    Posts
    180

    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
    Pete
    Struggling Solutions Architect

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,636

    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
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  4. #4
    Forum Contributor
    Join Date
    02-25-2012
    Location
    Coimbatore, India
    MS-Off Ver
    Excel 2010
    Posts
    445

    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. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,771

    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.


    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

  6. #6
    Forum Contributor
    Join Date
    06-23-2014
    Location
    Dubbo, Australia
    MS-Off Ver
    EXCEL 2019
    Posts
    180

    Re: removed repeated charecteristics with 1 creteria

    Hi Thilag,

    is this the sort of thing you are looking for?
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    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
    Excel Guru in the making

    <----------If the question has been answered to your satisfication please click the Add Repuation star to the left

  8. #8
    Forum Contributor
    Join Date
    06-23-2014
    Location
    Dubbo, Australia
    MS-Off Ver
    EXCEL 2019
    Posts
    180

    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. #9
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    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. #10
    Forum Contributor
    Join Date
    06-23-2014
    Location
    Dubbo, Australia
    MS-Off Ver
    EXCEL 2019
    Posts
    180

    Re: removed repeated charecteristics with 1 creteria

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

  11. #11
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    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. #12
    Forum Contributor
    Join Date
    06-23-2014
    Location
    Dubbo, Australia
    MS-Off Ver
    EXCEL 2019
    Posts
    180

    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. #13
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    Re: removed repeated charecteristics with 1 creteria

    Yeah best to wait

  14. #14
    Forum Contributor
    Join Date
    02-25-2012
    Location
    Coimbatore, India
    MS-Off Ver
    Excel 2010
    Posts
    445

    Re: removed repeated charecteristics with 1 creteria

    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

  15. #15
    Forum Contributor
    Join Date
    06-23-2014
    Location
    Dubbo, Australia
    MS-Off Ver
    EXCEL 2019
    Posts
    180

    Re: removed repeated charecteristics with 1 creteria

    all good bud

  16. #16
    Forum Contributor
    Join Date
    02-25-2012
    Location
    Coimbatore, India
    MS-Off Ver
    Excel 2010
    Posts
    445

    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. #17
    Forum Contributor
    Join Date
    02-25-2012
    Location
    Coimbatore, India
    MS-Off Ver
    Excel 2010
    Posts
    445

    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. #18
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    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. #19
    Forum Contributor
    Join Date
    02-25-2012
    Location
    Coimbatore, India
    MS-Off Ver
    Excel 2010
    Posts
    445

    Re: removed repeated charecteristics with 1 creteria

    TheCman81 file helped me

    thanks

    thilag

  20. #20
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    Re: removed repeated charecteristics with 1 creteria

    No problem, we got there in the end

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Vlookup with multiple creteria
    By sathiyamoorthy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-20-2013, 05:11 AM
  2. Replies: 14
    Last Post: 05-10-2013, 03:27 PM
  3. [SOLVED] Append data to repeated values with a fixed array to be repeated on value change
    By anchuri_chaitanya in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-06-2011, 12:30 AM
  4. Match data with 3 creteria..
    By sid_12 in forum Excel General
    Replies: 3
    Last Post: 07-29-2010, 04:44 AM
  5. Replies: 1
    Last Post: 02-12-2006, 11:30 PM

Bookmarks

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