IF statement with concatenate to show resubmitted work

[beginvbaanalyst]

Hi Everyone,

I'm trying to figure out a formula to show if a request was rejected or approved.
We make trackers twice a day and the only way to notice if a request is rejected, is if it shows up twice but with a different date.
Providing example

135969​	HIERARCHY	HIERARCHY	ARTICLE_CHANGE	11/10/2020​	Regular	Rejected
135969​		ARTICLE_CHANGE	ARTICLE_CHANGE	11/17/2020​	Regular	Rejected

Currently my if statement is the following:
=IF(COUNTIF(B:B,[@Formindex])>1,"Rejected","Approved")

I have 33 of these specific examples in my set of data; currently trying to build a dashboard but it's showing it was rejected twice which it isn't the case, maybe there is a way to show 10/10/2020 as rejected, and then 10/17/2020 as resubmitted?
That's a much better representation of what is actually happening with this data.
I don't thinking grouping them twice is a accurate representation.
Please help.
Thank you.

 

[Ezguy4u]

Begin, I would say we are going to need a little more information in order to give you a good solution. Also if you could use this, Read more about XL2BB..., to show your input and out put that would be great. One of my questions is that part "if it shows up twice". What does it refer to? Let this be the beginning of lots of questions and answers. Pretend we don't know any thing about what you are talking about. Actually you don't have to pretend.

 

[beginvbaanalyst]

Begin, I would say we are going to need a little more information in order to give you a good solution. Also if you could use this, Read more about XL2BB..., to show your input and out put that would be great. One of my questions is that part "if it shows up twice". What does it refer to? Let this be the beginning of lots of questions and answers. Pretend we don't know any thing about what you are talking about. Actually you don't have to pretend.

I know about XL2BB but I can't download on a company laptop, pasting fake data is the next best thing I can think of.
If it shows up twice it means the first index submission was rejected, the second index is a resubmission of the rejection.

In all I have three fields to show; approved, rejected, resubmitted.
I'm looking for a way to show the rejected status without grouping them together; currently they group because the index # is the same, the only difference is the date.

I keep thinking of an IF(OR) statement but idk if that's the best way to handle this situation.
I need the data to show an accurate representation of what goes on within our day to day work.

 

[Ezguy4u]

Ok Let's go down this road. Review the below sheet and comment if it is any thing like what you would expect. I am assuming the index # is unique. Let the questions and answers begin.

Book1
	A	B	C	D	E	F	G	H	I
1	Index #								Example
2	135950		ARTICLE_CHANGE	ARTICLE_CHANGE	11/2/2020	Regular	Approved		A
3	135678	HIERARCHY	HIERARCHY		11/3/2020	Regular	Rejected		B
4	135552	HIERARCHY	HIERARCHY		11/4/2020	Regular	Approved		C
5	135969	HIERARCHY	HIERARCHY		11/10/2020	Regular	Approved		D
6	135678		ARTICLE_CHANGE	ARTICLE_CHANGE	11/11/2020	Regular	Resubmitted		E
7	135969		ARTICLE_CHANGE	ARTICLE_CHANGE	11/10/2020	Regular	Approved		F
Status

 

[beginvbaanalyst]

Ok Let's go down this road. Review the below sheet and comment if it is any thing like what you would expect. I am assuming the index # is unique. Let the questions and answers begin.

Book1
	A	B	C	D	E	F	G	H	I
1	Index #								Example
2	135950		ARTICLE_CHANGE	ARTICLE_CHANGE	11/2/2020	Regular	Approved		A
3	135678	HIERARCHY	HIERARCHY		11/3/2020	Regular	Rejected		B
4	135552	HIERARCHY	HIERARCHY		11/4/2020	Regular	Approved		C
5	135969	HIERARCHY	HIERARCHY		11/10/2020	Regular	Approved		D
6	135678		ARTICLE_CHANGE	ARTICLE_CHANGE	11/11/2020	Regular	Resubmitted		E
7	135969		ARTICLE_CHANGE	ARTICLE_CHANGE	11/10/2020	Regular	Approved		F
Status

I was able to figure out a formula.
Thank you.
Have a great day!