+ Reply to Thread
Results 1 to 34 of 34

Need formula to identify duplicates and skip if values have been used

  1. #1
    Registered User
    Join Date
    12-29-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    21

    Need formula to identify duplicates and skip if values have been used

    I am trying to take rows of data, that are being exported from outside software and create a form for each row... kind of. We are a transport company and each row represents a trip for that passenger. I need the form to identify the passenger and all related trips for that person and combine them into one form. I can easily create formulas to do this for each trip separately, but I need a formula that can search the data for that passenger, combine them if they are related, and not have the next form create it again.

    The data looks like this:


    (Columns) Passenger | ID Number | Origin | Destination | Pick-Up Time | Drop-Off Time | Driver
    (Rows)
    Trips
    |
    |
    v


    There are many more columns that also need to be sorted into their respective cells on the form. I'm using two Sheets, one for the forms, and the other to reference from. I've figured out how to use INDIRECT to make it so I can copy and paste the form down the Sheet.

    I'm not sure where to begin with this. There are duplicate Passengers because each row represents a "one way" trip. So they will appear again for their "return trip" home. And in some cases they make and extra stop, so they may appear 3 or even 4 times. I need to combine them onto one form and have the next form identify that they've already been dealt with. All duplicates are sorted together since I'm sorting data A-Z by name.

    Below I have attached what I'm working on. It just has the basics of getting the information where I need it, and the copy and paste formula for new form on next page. But I've got a ways to go.
    Attached Files Attached Files
    Last edited by CKANE86; 12-31-2015 at 07:35 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need formula to identify duplicates and skip if values have been used

    If you already have a list of passengers and want to sum/count their info, you could use SUMIF or COUNTIF

    If you dont have that list of names and need to create that too, see if you can adapt this to suite...
    A
    B
    C
    2
    Tuesday
    Tuesday
    3
    Wednesday Wednesday
    4
    Thursday Thursday
    5
    Friday Friday
    6
    Saturday Saturday
    7
    Sunday Sunday
    8
    Monday Monday
    9
    Tuesday
    0
    10

    C2=IFERROR(INDEX($A$2:$A$24,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$24),),0)),"")
    copied down
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    12-29-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    21

    Re: Need formula to identify duplicates and skip if values have been used

    I have attached a copy of what I'm working on, it will explain what I'm trying to do. Sheet2 has some fake data to look at.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need formula to identify duplicates and skip if values have been used

    1. remove the merging you have in that form, it causes all sorts of problems with forumlas
    2. Can you add a helper column on sheet 2 to combine all those adresses?
    3. This ARRAY formula will pull in the data from column K for the name selected...
    =INDEX(Sheet2!K:K,SMALL(IF(Sheet2!$C$2:$C$10=Sheet1!$E$1,ROW(Sheet2!$A$2:$A$10)),ROWS($A$1:A1)))
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    ARRAY formulas can become very resource-intensive, so that is why I am suggesting that helper. You *could* combine a bunch of then to give the answer in your sample, but the helper will be simpler

  5. #5
    Registered User
    Join Date
    12-29-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    21

    Re: Need formula to identify duplicates and skip if values have been used

    Quote Originally Posted by FDibbins View Post
    1. remove the merging you have in that form, it causes all sorts of problems with forumlas
    2. Can you add a helper column on sheet 2 to combine all those adresses?
    3. This ARRAY formula will pull in the data from column K for the name selected...
    =INDEX(Sheet2!K:K,SMALL(IF(Sheet2!$C$2:$C$10=Sheet1!$E$1,ROW(Sheet2!$A$2:$A$10)),ROWS($A$1:A1)))
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    ARRAY formulas can become very resource-intensive, so that is why I am suggesting that helper. You *could* combine a bunch of then to give the answer in your sample, but the helper will be simpler
    So I was messing around with this formula you suggested, and have been trying to adapt it to what I need. I can add a column to sheet2 at the beginning or end of the data. So when you say helper column, how would I implement something like that.

    As far as working with your formula, I'm running into a REF error with what I've come up with. This is it right now:

    {=INDEX(Sheet2!J:J,SMALL(IF(INDIRECT("SHEET2!C"&ROUNDUP((ROW()-1)/47+1,0)):INDIRECT("SHEET2!C"&ROUNDUP((ROW()-1)/47+4,0))=E1,INDIRECT("SHEET2!A"&ROUNDUP((ROW()-1)/47+1,0)):INDIRECT("SHEET2!A"&ROUNDUP((ROW()-1)/47+4,0))),1))}

    So the first half of the Logical_test is coming up right with the 4 patients names, and matching it correctly to E1, but the way I have it written SMALL is returning the Trip ID instead of the REF row. I know it has gotta be something simple like using the wrong function.

    Also where I have E1, this was more complicated because I wanted it to adjust for copy and pasting the formula down the sheet, but when I set it up with INDIRECT like the rest, I was getting a Value ERROR there.
    Last edited by CKANE86; 01-04-2016 at 07:09 PM.

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need formula to identify duplicates and skip if values have been used

    I put this in AS2 and copied down...
    =K2&", "&L2&", "&M2&", "&N2&", "&O2

    Then, FIRST remove the merging from A8:A18, and enter this ARRAY formula in A8...
    =INDEX(Sheet2!$AS:$AS,SMALL(IF(Sheet2!$C$2:$C$10=Sheet1!$E$1,ROW(Sheet2!$A$2:$A$10)),ROWS($A$1:A1)))
    entered using CTRL SHIFT ENTER, not just enter

    This is teh same formula I suggested above, except I am using AS (the helper column)

  7. #7
    Registered User
    Join Date
    12-29-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    21

    Re: Need formula to identify duplicates and skip if values have been used

    Quote Originally Posted by FDibbins View Post
    I put this in AS2 and copied down...
    =K2&", "&L2&", "&M2&", "&N2&", "&O2

    Then, FIRST remove the merging from A8:A18, and enter this ARRAY formula in A8...
    =INDEX(Sheet2!$AS:$AS,SMALL(IF(Sheet2!$C$2:$C$10=Sheet1!$E$1,ROW(Sheet2!$A$2:$A$10)),ROWS($A$1:A1)))
    entered using CTRL SHIFT ENTER, not just enter

    This is teh same formula I suggested above, except I am using AS (the helper column)
    Thanks for for all the help so far FDibbins, definitely feels like I'm getting somewhere. I've been working with the formula's you've given me and everything seems to be working.

    I'm noticing a problem that may occur with my data though, we're using the Trip ID to reference the row, to set the order of the trips on the document. This will work in most cases, but there are some cases where the lower ID is the return trip or "will-call", and it will be sorted incorrectly. Looking at my Data the "Request Type" Column is what I think I should use to correct this. I've updated the Form and attached it below, implementing what you've helped me with so far, and gave an example on Sheet2, "Joan Mill".

    Form 1.1.xlsx

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need formula to identify duplicates and skip if values have been used

    I have added another helper to pull sequentially. (I hid some columns so I could see all the data on 1 screen, you can unhide them). I also changed my formulas a bit to - hopefully - give you what you wanted.

    See the attached
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    12-29-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    21

    Re: Need formula to identify duplicates and skip if values have been used

    Hmm. I'm trying to understand what it is your trying to do. Were you trying to show me what to do under the "TRANSPORT FROM"? I just want to clarify what I'm trying to accomplish.

    Under "TRANSPORT FROM" there will only be one trip, we pick them up from home, then "TRANSPORT TO" appointment 1, 2, 3 etc., then home again. The trip home is reflected below those sections by checking the "ROUND TRIP" box.

    So using Joan Mill as the example again, the address that belongs under the "TRANSPORT TO" section has the lower ID, so It would appear under "TRANSPORT FROM" using that formula. Where most of the time the trip with the lowest ID would be the first pick-up(usually reflected as Request Type: drop-off or pick-up; will-calls are always the return trip home or to the next appointment), in this case it's not. And this will appear occasionally as Data is exported from our software.

    Also, I need the formula copy and paste friendly. That's why I edited to:

    A8: {=UPPER(IFERROR(INDEX(Sheet2!$AS:$AS,SMALL(IF(Sheet2!$C$2:$C$200=LOOKUP("Sheet1!$E"&ROUNDUP((ROW()-7)/47,0),Sheet1!$E$1:Sheet1!$E$200),ROW(Sheet2!$A$2:$A$200)),1)),""))}

    If you scroll down on Sheet1, you can see the outline of the next form, that is where I'll be pasting, and I'll paste down like that another 200 times.

    I'm getting very close I think. What I need now is another variable to add to that formula to identify will-call's that have lower Trip ID's like Joan Mill.

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need formula to identify duplicates and skip if values have been used

    OK wait, Im confused. I thought you wanted to pull out all trips for a name, and then have them sorted (small to large) based on Trip ID?

    That is what I have done. Helper1 combined the address and helper2 pulls out the only the Trip ID for that name

    Then in sheet1, I am pulling out that name, sorted by Trip ID

  11. #11
    Registered User
    Join Date
    12-29-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    21

    Re: Need formula to identify duplicates and skip if values have been used

    Yeah, I thought we might have been going different directions there. So yeah in a sense, that is what I would like to do. But the data is organized by the first pick up location under "TRANSPORT FROM" and anything else related would be under "TRANSPORT TO". This is going to be a complicated form to create...

    There are some many variables, I have might a passenger going to and from an appointment twice in one day, and those would need to be on separate forms, or that passenger may go to 2 places before returning home, and that needs to be on one form. And there are cases when we take someone only one way, so I would also need to identify that, and use both addresses in the row to create the form.

    Is that making any sense? This is my first time using Excel for formulas like this, and I'm trying to explain best I can. I've been teaching myself just these last couple weeks.

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need formula to identify duplicates and skip if values have been used

    Yes it would seem I did note quite get what you wanted

    Can you upload some more sample answers for me? (just use 1 column for the addy, we already know how to combine those)

  13. #13
    Registered User
    Join Date
    12-29-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    21

    Re: Need formula to identify duplicates and skip if values have been used

    Alright, I've made some changes to the file. I'll explain how the data on Sheet2 should appear on the form on Sheet1.

    So John Doe is what we call a Round Trip with an XTRA Leg. It's Round Trip because he returned home, and is XTRA Leg because there is more than one stop. So all three rows for him belong on one form, with his home address being under "Transport From" and each appointment under "Transport To".

    Jill Gal is a One-way, she appears once, picked up from hospital and taken to rehab facility. So this needs to appear on one form. Hospital address under "Transport From" and facility under "Transport To".

    Joan Mill is just a Round Trip, with the added complexity of the Trip ID for her Will-call being lower. Again, home address under "Transport From".

    Harry Guy is a new example I'm providing you with. He has two Round Trips in one day. So they need to be separate on two forms.


    So on Sheet1, you can see how John Doe appears on the form, that is how I need the information to appear for each passenger, one passenger per form, one round trip or one-way per form.

    I was thinking of how to arrange these properly, because of the Trip ID not always being lowest for the trip that belongs under "Transport From". I figured that I can use the Scheduled pick up or drop-off times in columns AC and AD instead. Those will always be correct.

    Form 1.2.xlsx
    Last edited by CKANE86; 01-07-2016 at 05:20 PM.

  14. #14
    Registered User
    Join Date
    12-29-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    21

    Re: Need formula to identify duplicates and skip if values have been used

    I've been working and working at this, I can't seem to get it to do what I need it to do. I moved "Harry Guy" to the top of the list on Sheet2 so I could trial and error sorting his two round trips on separate forms, but I'm just plugging in formulas and not getting anywhere. I think I need to add another helper column to help identify trips that belong on separate forms, I've updated the form on my last post with my example. Still not sure where to go. I get lost in a mess of IF functions for the new helper column "Trip Organizer" on Sheet2.

  15. #15
    Registered User
    Join Date
    05-03-2011
    Location
    https://t.me/pump_upp
    MS-Off Ver
    Excel 2016
    Posts
    98

    Re: Need formula to identify duplicates and skip if values have been used

    Hi Friend...

    I think you are need to filtered the data one trip , pickup and etc. am i right.

    if one page you need to only pickup, and another u need to two rounds.

  16. #16
    Registered User
    Join Date
    12-29-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    21

    Re: Need formula to identify duplicates and skip if values have been used

    Quote Originally Posted by rukman View Post
    Hi Friend...

    I think you are need to filtered the data one trip , pickup and etc. am i right.

    if one page you need to only pickup, and another u need to two rounds.
    If you look at the file I attach on post #13, on Sheet2 you'll see the passenger name Harry Guy come up on 4 rows. Each one of those represents a one way trip for him, but all together that is two round trips. I need some way to identify that, and sort them onto different forms on sheet1. Right now you can see with what I have it puts all for trips onto the first form on sheet1(i haven't set up the rest of forms below because I haven't figured out how to sort it correctly).

    If you look at Sheet2 again, you'll see that some passengers might have just 1 trip, which is just a one-way trip, and some will have 3 trips, which is a round trip(round trip is from home to appointment, then home again) with and extra stop, that all 3 would appear on one form.

    That's why I've created the "Trip Organizer" column on sheet2. I need a formula there that will identify each trip that is related using some kind of letter or number, that I can use on sheet1 to identify with to keep things sorted correctly. To do this, the formula has to compare passengers names with the addresses, to come with with something like:

    Passenger Name .............. Trip Organizer
    Harry Guy----------------------1
    Harry Guy----------------------1
    Harry Guy----------------------2
    Harry Guy----------------------2
    Jill Gal--------------------------3
    Joan Mill------------------------4
    Joan Mill------------------------4


    That way each form pulls from the trip organizer column to then pull the rest of the data over.
    Last edited by CKANE86; 01-07-2016 at 07:34 PM.

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need formula to identify duplicates and skip if values have been used

    Lets take this 1 step at a time, and (for now) we will keep it simple - the fancy cosmetics we can apply later, all they do now if muddy things up
    (I moved the Destination Helper over, we can deal with that later)

    1. You want John Doe to appear on form1 for all 3 of his entries
    AS2=IF(Sheet2!A2="","",Sheet2!K2&", "&Sheet2!L2&", "&Sheet2!M2&", "&Sheet2!N2&" "&Sheet2!O2)
    AT2=IF(C2<>Sheet1!$E$1,"",A2)
    sheet1
    A8=IFERROR(INDEX(Sheet2!$AS:$AS,MATCH(SMALL(Sheet2!$AT:$AT,ROWS($A$1:A1)),Sheet2!$A:$A,0)),"")
    This will pull in 3 entries for John

    2. This also pulls in 1 entry for Jill Gal
    3. Joan Mill also brings in 2 entries (we can get to the "home" bit)
    4. Harry has 2 trip ID's only, I know this is dummy data, but is that how it will be?

  18. #18
    Registered User
    Join Date
    05-03-2011
    Location
    https://t.me/pump_upp
    MS-Off Ver
    Excel 2016
    Posts
    98

    Re: Need formula to identify duplicates and skip if values have been used

    I attached 1 excel file. it is a sample file.I think these codes to help you.
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    12-29-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    21

    Re: Need formula to identify duplicates and skip if values have been used

    Quote Originally Posted by FDibbins View Post
    Lets take this 1 step at a time, and (for now) we will keep it simple - the fancy cosmetics we can apply later, all they do now if muddy things up
    (I moved the Destination Helper over, we can deal with that later)

    1. You want John Doe to appear on form1 for all 3 of his entries
    AS2=IF(Sheet2!A2="","",Sheet2!K2&", "&Sheet2!L2&", "&Sheet2!M2&", "&Sheet2!N2&" "&Sheet2!O2)
    AT2=IF(C2<>Sheet1!$E$1,"",A2)
    sheet1
    A8=IFERROR(INDEX(Sheet2!$AS:$AS,MATCH(SMALL(Sheet2!$AT:$AT,ROWS($A$1:A1)),Sheet2!$A:$A,0)),"")
    This will pull in 3 entries for John

    2. This also pulls in 1 entry for Jill Gal
    3. Joan Mill also brings in 2 entries (we can get to the "home" bit)
    4. Harry has 2 trip ID's only, I know this is dummy data, but is that how it will be?
    Harry's trip ID's will not be the same like that, every line will have a different number. To keep a long story short, I can't use Trip ID to sort the order of the address on Sheet1. Need to use column AC on sheet2, that will work fine. So should the formula look like this?

    =IFERROR(INDEX(Sheet2!$AS:$AS,MATCH(SMALL(Sheet2!$AC:$AC,ROWS($A$1:A1)),Sheet2!$AC:$AC,0)),"")

  20. #20
    Registered User
    Join Date
    12-29-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    21

    Re: Need formula to identify duplicates and skip if values have been used

    I found my own mistake, still learning here
    The formula would be:

    A8: {=IFERROR(INDEX(Sheet2!$AS:$AS,MATCH(SMALL(Sheet2!$AV:$AV,ROWS($A$1:A1)),Sheet2!$AC:$AC,0)),"")}
    AV2: =IF(C2<>Sheet1!$E$1,"",AC)

    Here is the copy of the form that goes with that formula.

    Form 1.2.xlsx
    Last edited by CKANE86; 01-08-2016 at 03:38 PM.

  21. #21
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need formula to identify duplicates and skip if values have been used

    So are you saying that solves your question, or is that still not doing what you want?

    (keep proding me, I work with a lot of threads, and cant always find threads I have worked on)

  22. #22
    Registered User
    Join Date
    12-29-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    21

    Re: Need formula to identify duplicates and skip if values have been used

    Well that is one part of what I need. I believe the next step would be the Trip Organizer column, I'm going to use that to help the form on sheet1 identify what passenger name it places in Patient Name & Phone on sheet1. I'm looking for somthing like(in order down column AU): 1,1,2,2,3,4,4,5,5,5. So the formulas know only to pull information from rows with the same number.
    Last edited by CKANE86; 01-08-2016 at 07:07 PM.

  23. #23
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need formula to identify duplicates and skip if values have been used

    How will we know that this individual needs to have extra treatment (what am I missing here?)

    Doing this manually, what would the steps be?

  24. #24
    Registered User
    Join Date
    12-29-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    21

    Re: Need formula to identify duplicates and skip if values have been used

    This is really hard to explain lol. But you can tell by looking at the addresses and pick up times. In the addresses we combined in column AS, normally if a patient just had 1 round trip for the day, that address would only appear once in that column(same for a one-way trip like Jill Gal, and a round trip with an XTRA Leg like John Doe), more than 1 round trip in a day would cause their home address to appear more than once in that column. So using that information, label all trips that belong on that form under the "Trip Organizer" Column using a 1, then the next set would use 2, and so on and so forth. So in the case of Harry Guy, you can tell he had 2 round trips in one day, and the pick up times that are closer together would be the related trips. Trips marked with a 1 would only appear on that form1, then the next number would go on the next form.

    I can't not for the life of me figure out the formula to compare addresses that belong to that name with other addresses of that name in column AS, then identify 1 or 2 or more round trips for that day, then place the number accordingly. With the right formula the numbers under "Trip Organizer" would look like: 1,1,2,2,3,4,4,5,5,5(going down the rows), if looking at the most recent file in post 20
    Last edited by CKANE86; 01-08-2016 at 08:25 PM.

  25. #25
    Registered User
    Join Date
    12-29-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    21

    Re: Need formula to identify duplicates and skip if values have been used

    Oh, and it has to only look for the trips with the earlier pick up times(because that would be their home), since a lot of our passengers visit the same places.

  26. #26
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need formula to identify duplicates and skip if values have been used

    I can't not for the life of me figure out the formula to compare addresses that belong to that name with other addresses of that name in column AS, then identify 1 or 2 or more round trips for that day, then place the number accordingly
    would this work?
    =IF(AS2="","",COUNTIFS($AS$2:AS2,AS2,$C$2:C2,C2))

    It will progressively count multiple "home addy" for the person, as you copy it down

    Maybe this will get us started on the next part?

    (sorry Im being a bit thick on this lol)

  27. #27
    Registered User
    Join Date
    12-29-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    21

    Re: Need formula to identify duplicates and skip if values have been used

    Quote Originally Posted by FDibbins View Post
    would this work?
    =IF(AS2="","",COUNTIFS($AS$2:AS2,AS2,$C$2:C2,C2))

    It will progressively count multiple "home addy" for the person, as you copy it down

    Maybe this will get us started on the next part?

    (sorry Im being a bit thick on this lol)
    No worries FDibbins, you're helping me if it makes sense or not

    So adapting the formula to my needs, I've come up with: =IF(Sheet2!C3="","",IF(Sheet2!C3=Sheet2!C2,IF(COUNTIFS(Sheet2!$AS:$AS,Sheet2!$AS3)>1,Sheet2!AU2+1,Sheet2!AU2),1+Sheet2!AU2))

    But that's not quite where I need it yet. The underlined part still needs work. I need the array AS:AS to change to the range of where that passenger's name is. (ie. AS2:AS5; for Harry Guy)
    Last edited by CKANE86; 01-11-2016 at 03:56 PM.

  28. #28
    Registered User
    Join Date
    12-29-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    21

    Re: Need formula to identify duplicates and skip if values have been used

    I think it would look something like this:
    =IF(Sheet2!C3="","",IF(Sheet2!C3=Sheet2!C2,IF(COUNTIFS(Sheet2!$AS&ROW(MATCH(SHEET2!C3,SHEET2!C:C,0)):$AS&ROW(MATCH(SHEET2!C3,SHEET2!C:C)),Sheet2!$AS3)>1,Sheet2!AU2+1,Sheet2!AU2),1+Sheet2!AU2))

    I know there is something wrong with it though, I just don't know how to make it work.

  29. #29
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need formula to identify duplicates and skip if values have been used

    I will take a look for you

  30. #30
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need formula to identify duplicates and skip if values have been used

    Why did you change the formula, what is that trying to do? My understanding is that you want to group/ID like-departures together, that is what this is doing. Once we have a sequence of numbers next to them, we can extract based on that sequence. You can add in the test for the name (C2), but I really dont understand what you are doing there.

    AS
    AT
    AU
    1
    Origin Address Helper Destination Address Helper Trip Organizer
    2
    555 Apartment, Apt 10, Make believe, ca 95555 555 Dr. Office, Suite A, Make Believe, ca 95555
    1
    3
    555 Dr. Office, Suite A, Make Believe, ca 95555 555 Apartment, Apt 10, Make believe, ca 95555
    1
    4
    555 Apartment, Apt 10, Make believe, ca 95555 555 Dr. Office 2, Suite B, Make Believe, ca 95555
    2
    5
    555 Dr. Office 2, Suite B, Make Believe, ca 95555 555 Apartment, Apt 10, Make believe, ca 95555
    1
    6
    555 Hospital, Room 100, Make believe, ca 95555 555 Rehab, Make Believe, ca 95555
    1
    7
    555 Dr. Office 1, Suite A, Make Believe, ca 95555 222 Home Ave., Make Believe, ca 95555
    1
    8
    222 Home Ave., Make Believe, ca 95555 555 Dr. Office 1, Suite A, Make Believe, ca 95555
    1
    9
    555 Home St., Make believe, ca 95555 555 Office, suite A, Make Believe, ca 95555
    1
    10
    555 Office, Suite A, Make Believe, ca 95555 555 Office 2, Suite B, Make believe, ca 95555
    1
    11
    555 Office 2, Suite B, Make Believe, ca 95555 555 Home St., Make believe, ca 95555
    1

    Once you have that, you can pull out (the 1st)1, then (the 1st) 2 etc all other 1's and 2's will be ignored

  31. #31
    Registered User
    Join Date
    12-29-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    21

    Re: Need formula to identify duplicates and skip if values have been used

    Maybe I've over thinking it. I think we have the same idea, we're just visualizing it differently. I was thinking of numbers that continually count up (identifying what # of round trip it is on the list). But I guess having just 1s and 2s can accomplish the same thing. So using what you have there, how do i pull harry guy's first two trips, and his name, then go down to the next form, pull his name and next 2 trips, and move on to next person?

  32. #32
    Registered User
    Join Date
    12-29-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    21

    Re: Need formula to identify duplicates and skip if values have been used

    This what I was thinking, using that formula I posted, am I over doing it?

    In AU2 I just have =1 because that's what it will always be.

    AS AT AU
    555 Apartment, Apt 10, Make believe, ca 95555 555 Dr. Office, Suite A, Make Believe, ca 95555 1
    555 Dr. Office, Suite A, Make Believe, ca 95555 555 Apartment, Apt 10, Make believe, ca 95555 1
    555 Apartment, Apt 10, Make believe, ca 95555 555 Dr. Office 2, Suite B, Make Believe, ca 95555 2
    555 Dr. Office 2, Suite B, Make Believe, ca 95555 555 Apartment, Apt 10, Make believe, ca 95555 2
    555 Hospital, Room 100, Make believe, ca 95555 555 Rehab, Make Believe, ca 95555 3
    555 Dr. Office 1, Suite A, Make Believe, ca 95555 222 Home Ave., Make Believe, ca 95555 4
    222 Home Ave., Make Believe, ca 95555 555 Dr. Office 1, Suite A, Make Believe, ca 95555 4
    555 Home St., Make believe, ca 95555 555 Office, suite A, Make Believe, ca 95555 5
    555 Office, Suite A, Make Believe, ca 95555 555 Office 2, Suite B, Make believe, ca 95555 5
    555 Office 2, Suite B, Make Believe, ca 95555 555 Home St., Make believe, ca 95555 5

  33. #33
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need formula to identify duplicates and skip if values have been used

    Just to keep this on the radar...

    I will need to take a step back and look at this again, my mind is running in circles lol

  34. #34
    Registered User
    Join Date
    12-29-2015
    Location
    United States
    MS-Off Ver
    2013
    Posts
    21

    Re: Need formula to identify duplicates and skip if values have been used

    Quote Originally Posted by FDibbins View Post
    Just to keep this on the radar...

    I will need to take a step back and look at this again, my mind is running in circles lol
    No problem, actually I have been making a ton of progress, and I may even be done. I just need the software we're using to export some additional data that it does not, and I'm taking that up with them. Here is what I've got:
    Form 1.5 Fake.xlsx

    Sheet one now has forms going down far enough to use all of the data we've been trying to sort. And everything is working perfectly! I will do some testing with real data soon, that should give me an idea if there are anymore issues that will come up(there is about 200rows of real data). So can you could take a look at my formulas on page 1 sheet1 and see if there any ways to simplify them? I've got them to work but that doesn't mean that it's the most efficient way.
    Last edited by CKANE86; 01-15-2016 at 04:16 PM.

+ 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. [SOLVED] Formula to identify duplicates in the same column
    By Stephen R in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-19-2015, 01:10 PM
  2. [SOLVED] How to identify Duplicates in a column using Formula (Not filters)
    By karthikskengeri in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-20-2013, 08:06 AM
  3. Identify duplicates values
    By SunCityAZDL in forum Excel General
    Replies: 6
    Last Post: 05-07-2013, 06:35 PM
  4. [SOLVED] Identify Duplicates and Non-Duplicates within an Array (Match)
    By simpson11 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-26-2013, 09:49 AM
  5. Replies: 2
    Last Post: 12-21-2012, 12:42 AM
  6. [SOLVED] Transfer cell values to another sheet, but need to skip duplicates
    By VBA FTW in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-04-2012, 06:20 PM
  7. Use formula to identify duplicates
    By lizzae in forum Excel General
    Replies: 3
    Last Post: 09-10-2009, 01:15 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