Return value based off of earliest Date and Criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-1380862%22%20slang%3D%22en-US%22%3EReturn%20value%20based%20off%20of%20earliest%20Date%20and%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1380862%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20realize%20there%20may%20be%20a%20similar%20question%20out%20there%20but%20had%20trouble%20finding%20exactly%20what%20I%20was%20looking%20for%20so%20I%20hope%20someone%20here%20can%20help!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20to%20return%20the%20earliest%20stage%20based%20off%20of%20certain%20criteria.%20In%20the%20data%2C%20I%20have%20Account%20data%20for%20different%20accounts%20(Column%20C).%20In%20Column%20E%20I%20have%20the%20initial%20Stage%20Date%2C%20which%20is%20the%20first%20time%20the%20Stage%20was%20set%20for%20that%20account.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I'm%20looking%20for%3A%20I'm%20looking%20for%20the%20first%20Stage%20that%20was%20set%20on%20the%20account.%20For%20example%2C%20%22Dog%22%20was%20a%20Customer%20-Lost%20stage%20and%20I%20want%20to%20show%20that%20on%20Column%20F%20in%20F5%20and%20F6.%20I%20am%20able%20to%20gather%20the%20initial%20stage%20date%20and%20would%20like%20to%20use%20the%20initial%20stage%20date%20and%20account%20name%20to%20inform%20me%20what%20the%20initial%20stage%20was%20of%20the%20account.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20that%20helps.%20I%20have%20attached%20example%20data%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20so%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1380862%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1381237%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20value%20based%20off%20of%20earliest%20Date%20and%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1381237%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F657996%22%20target%3D%22_blank%22%3E%40alexandrabiorka%3C%2FA%3E%26nbsp%3BWell%20this%20is%20probably%20not%20what%20you%20are%20looking%20for.%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I%20would%20just%20a%20custom%20sort.%20Sort%20it%20by%20customer%20and%20then%20add%20a%20level%20by%20Date.%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20will%20show%20you%20in%20order%20the%20first%20stage%20that%20comes%20up%20for%20a%20customer.%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20take%20it%20further%20if%20you%20wanted.%20You%20could%20then%20use%20a%20formula%20in%20the%20far%20right%20column%20(adding%20a%20new%20column)%20that%20does%20an%20if%20statement.%20If%20will%20say%20if%20the%20customer%20of%20the%20field%20I%20am%20on%20is%20the%20same%20as%20the%20customer%20on%20the%20line%20above%2C%20then%20return%20blank.%20If%20the%20customer%20is%20new%2C%20then%20bring%20in%20the%20stage.%20The%20result%20in%20this%20column%20will%20be%20it%20will%20only%20show%20the%20first%20stage%20name%20once.%20All%20the%20other%20stages%20should%20be%20blank.%26nbsp%3B%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3E1.%20Tom%20Jones%20-%20Lead%3C%2FP%3E%3CP%3E2.%20Tom%20Jones%20-%20Customer%20won%3C%2FP%3E%3CP%3E3.%20Ed%20Jones%20-%20Lead%20-%20Next%20column%20says%2C%20if%20column%202%20is%20Ed%20Jones%20then%20blank.%20If%20not%20then%20bring%20in%20intial%20phase%20of%20Lead.%20(You%20will%20have%20to%20do%20the%20If%20statement.%20I%20am%20just%20writing%20out%20the%20example).%26nbsp%3B%3C%2FP%3E%3CP%3E4.%20Ed%20Jones%20-%20Customer%20won%20-%20Same%20formula.%20But%20since%203%20is%20the%20same%20as%204%20it%20will%20be%20blank.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyhow.%20Hope%20you%20can%20follow.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1381283%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20value%20based%20off%20of%20earliest%20Date%20and%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1381283%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F657996%22%20target%3D%22_blank%22%3E%40alexandrabiorka%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlace%20this%20formula%20in%20F2%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DVLOOKUP(E2%26amp%3BC2%2CCHOOSE(%7B1%2C2%7D%2C%24B%242%3A%24B%2413%26amp%3B%24C%242%3A%24C%2413%2C%24D%242%3A%24D%2413)%2C2%2C0)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1381348%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20value%20based%20off%20of%20earliest%20Date%20and%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1381348%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F428790%22%20target%3D%22_blank%22%3E%40Patrick2788%3C%2FA%3E%26nbsp%3B-%20thanks%20for%20your%20help!%20This%20unfortunately%20did%20not%20work%20for%20me.%20I%20am%20getting%20N%2FAs%20and%20only%20Customer%20Closed%20Won.%20I%20just%20made%20sure%20that%20the%20date%20formats%20match%20as%20well.%20Please%20see%20below%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22alexandrabiorka_0-1589242174811.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F190796i1D61B2A01F15779B%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22alexandrabiorka_0-1589242174811.png%22%20alt%3D%22alexandrabiorka_0-1589242174811.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1381352%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20value%20based%20off%20of%20earliest%20Date%20and%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1381352%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F587481%22%20target%3D%22_blank%22%3E%40TheFunOne_pdx%3C%2FA%3E%26nbsp%3B-%20thanks%20for%20taking%20a%20look%20and%20helping%20me%20out!%20I've%20given%20it%20a%20shot%20but%20I%20don't%20think%20I%20follow.%20I've%20attached%20my%20sample%20dataset%2C%20would%20you%20be%20able%20to%20help%20me%20out%20by%20showing%20me%20an%20example%3F%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1382705%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20value%20based%20off%20of%20earliest%20Date%20and%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1382705%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F657996%22%20target%3D%22_blank%22%3E%40alexandrabiorka%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20does%20your%20formula%20look%20like%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hello,

 

I realize there may be a similar question out there but had trouble finding exactly what I was looking for so I hope someone here can help! 

 

I am looking to return the earliest stage based off of certain criteria. In the data, I have Account data for different accounts (Column C). In Column E I have the initial Stage Date, which is the first time the Stage was set for that account.

 

What I'm looking for: I'm looking for the first Stage that was set on the account. For example, "Dog" was a Customer -Lost stage and I want to show that on Column F in F5 and F6. I am able to gather the initial stage date and would like to use the initial stage date and account name to inform me what the initial stage was of the account.

 

I hope that helps. I have attached example data here.

 

Thanks so much!

5 Replies
Highlighted

@alexandrabiorka Well this is probably not what you are looking for. 

But I would just a custom sort. Sort it by customer and then add a level by Date. 

This will show you in order the first stage that comes up for a customer. 

You could take it further if you wanted. You could then use a formula in the far right column (adding a new column) that does an if statement. If will say if the customer of the field I am on is the same as the customer on the line above, then return blank. If the customer is new, then bring in the stage. The result in this column will be it will only show the first stage name once. All the other stages should be blank. 

Example:

1. Tom Jones - Lead

2. Tom Jones - Customer won

3. Ed Jones - Lead - Next column says, if column 2 is Ed Jones then blank. If not then bring in intial phase of Lead. (You will have to do the If statement. I am just writing out the example). 

4. Ed Jones - Customer won - Same formula. But since 3 is the same as 4 it will be blank. 

 

Anyhow. Hope you can follow. 

 

Highlighted

@alexandrabiorka 

Place this formula in F2:

=VLOOKUP(E2&C2,CHOOSE({1,2},$B$2:$B$13&$C$2:$C$13,$D$2:$D$13),2,0)

Highlighted

Hi @Patrick2788 - thanks for your help! This unfortunately did not work for me. I am getting N/As and only Customer Closed Won. I just made sure that the date formats match as well. Please see below 

 

alexandrabiorka_0-1589242174811.png

 

Highlighted

@TheFunOne_pdx - thanks for taking a look and helping me out! I've given it a shot but I don't think I follow. I've attached my sample dataset, would you be able to help me out by showing me an example? Thank you!

Highlighted

@alexandrabiorka 

What does your formula look like?