Comparing 2 worksheets with different rows with conditions

[Country_Calc_2]

I am trying to compare 2 different worksheets to see whether the RFI (Column A - request for information) led to the RFP (Column C- request for proposal) for counts of conversion (RFI came before RFP) and wins (closed won).

Each worksheet will have different number of rows.

I put these on a single worksheet for example purposes.

Columns in red (F,G,H) would ideally all be formulas. Go I guess I am requesting 3 formulas.

Please let me know if I can provide any more info.

RFI Client	RFI Created Date	RFP Client	RFP Created Date	RFP Stage	Result	RFI Converted	RFI Won
Client A	1/1/2022​	Client B	1/1/2022​	Closed Lost	Client A	2​	1​
Client B	1/1/2024​	Client A	1/1/2023​	Closed Lost	Client B	0​	0​
Client C	1/1/2024​	Client A	3/1/2024​	Closed Won	Client C	0​	0​
Client D	1/1/2023​	Client D	6/1/2023​	Closed Lost	Client D	1​	0​
Client C	1/1/2023​	Client C	1/1/2022​	Closed Lost

 

[Eric W]

Maybe:

david763.xlsx
	A	B	C	D	E	F	G	H
1	RFI Client	RFI Created Date	RFP Client	RFP Created Date	RFP Stage	Result	RFI Converted	RFI Won
2	Client A	1/1/2022	Client B	1/1/2022	Closed Lost	Client A	2	1
3	Client B	1/1/2024	Client A	1/1/2023	Closed Lost	Client B	0	0
4	Client C	1/1/2024	Client A	3/1/2024	Closed Won	Client C	0	0
5	Client D	1/1/2023	Client D	6/1/2023	Closed Lost	Client D	1	0
6	Client C	1/1/2023	Client C	1/1/2022	Closed Lost
Sheet4

Cell Formulas
Range	Formula
F2:F5	F2	=LET(a,UNIQUE(A2:A10),SORT(FILTER(a,a<>"")))
G2:G5	G2	=LET(a,SORT(FILTER($B$2:$B$10,$A$2:$A$10=F2)),s,SEQUENCE(ROWS(a)),SUM(COUNTIFS($C$2:$C$15,F2,$D$2:$D$15,">="&INDEX(a,s),$D$2:$D$15,"<"&IFERROR(INDEX(a,s+1),9^9))))
H2:H5	H2	=LET(a,SORT(FILTER($B$2:$B$10,$A$2:$A$10=F2)),s,SEQUENCE(ROWS(a)),SUM(COUNTIFS($C$2:$C$15,F2,$D$2:$D$15,">="&INDEX(a,s),$D$2:$D$15,"<"&IFERROR(INDEX(a,s+1),9^9),$E$2:$E$15,"*Won")))
Dynamic array formulas.

 

[MountainFog]

I'm confused by your dataset. There is only 1 Client A RFI, but 2 RFI Converted reads 2. RFP Created has 2 client As. Does that make the RFI Client sheet irrelevant?

 

[Country_Calc_2]

I'm confused by your dataset. There is only 1 Client A RFI, but 2 RFI Converted reads 2. RFP Created has 2 client As. Does that make the RFI Client sheet irrelevant?

No, because for my dataset there are be multiple RFPs that come from a single RFI.

 

[Country_Calc_2]

Maybe:

david763.xlsx
	A	B	C	D	E	F	G	H
1	RFI Client	RFI Created Date	RFP Client	RFP Created Date	RFP Stage	Result	RFI Converted	RFI Won
2	Client A	1/1/2022	Client B	1/1/2022	Closed Lost	Client A	2	1
3	Client B	1/1/2024	Client A	1/1/2023	Closed Lost	Client B	0	0
4	Client C	1/1/2024	Client A	3/1/2024	Closed Won	Client C	0	0
5	Client D	1/1/2023	Client D	6/1/2023	Closed Lost	Client D	1	0
6	Client C	1/1/2023	Client C	1/1/2022	Closed Lost
Sheet4

Cell Formulas
Range	Formula
F2:F5	F2	=LET(a,UNIQUE(A2:A10),SORT(FILTER(a,a<>"")))
G2:G5	G2	=LET(a,SORT(FILTER($B$2:$B$10,$A$2:$A$10=F2)),s,SEQUENCE(ROWS(a)),SUM(COUNTIFS($C$2:$C$15,F2,$D$2:$D$15,">="&INDEX(a,s),$D$2:$D$15,"<"&IFERROR(INDEX(a,s+1),9^9))))
H2:H5	H2	=LET(a,SORT(FILTER($B$2:$B$10,$A$2:$A$10=F2)),s,SEQUENCE(ROWS(a)),SUM(COUNTIFS($C$2:$C$15,F2,$D$2:$D$15,">="&INDEX(a,s),$D$2:$D$15,"<"&IFERROR(INDEX(a,s+1),9^9),$E$2:$E$15,"*Won")))
Dynamic array formulas.

Thanks so much for this. It took me awhile to setup because I was trying to reference entire columns on other worksheets and excel did not like it. When I used the exact ranges it worked.

 

[Eric W]

I could set it up to use entire column references if you want, but it might slow down your sheet. Let me know.

Glad we could help.

 

[Country_Calc_2]

A version that references columns/named range that is a column would be very helpful as the dataset is not that large but will be constantly changing.

 

[Country_Calc_2]

A version that references columns/named range that is a column would be very helpful as the dataset is not that large but will be constantly changing.

My example had an equal number of rows for both datasets. When I used my real data I noticed it was not picking up everything. I had to fill the dataset which had fewer rows with dummy data to equal the other one to fix. Just wanted to point out in case someone else has the same issue - but formula works great, reading a full column would be extremely useful if possible.

 

[Eric W]

Here's how to do it with whole column references:

david763.xlsx
	A	B	C	D	E	F	G	H
1	RFI Client	RFI Created Date	RFP Client	RFP Created Date	RFP Stage	Result	RFI Converted	RFI Won
2	Client A	1/1/2022	Client B	1/1/2022	Closed Lost	Client A	2	1
3	Client B	1/1/2024	Client A	1/1/2023	Closed Lost	Client B	0	0
4	Client C	1/1/2024	Client A	3/1/2024	Closed Won	Client C	0	0
5	Client D	1/1/2023	Client D	6/1/2023	Closed Lost	Client D	1	0
6	Client C	1/1/2023	Client C	1/1/2022	Closed Lost
Sheet4

Cell Formulas
Range	Formula
F2:F5	F2	=LET(a,UNIQUE(A:A),SORT(FILTER(a,(a<>"")*(a<>"RFI Client"))))
G2:G5	G2	=LET(a,SORT(FILTER($B:$B,$A:$A=$F2)),s,SEQUENCE(ROWS(a)),SUM(COUNTIFS($C:$C,$F2,$D:$D,">="&INDEX(a,s),$D:$D,"<"&IFERROR(INDEX(a,s+1),9^9))))
H2:H5	H2	=LET(a,SORT(FILTER($B:$B,$A:$A=$F2)),s,SEQUENCE(ROWS(a)),SUM(COUNTIFS($C:$C,$F2,$D:$D,">="&INDEX(a,s),$D:$D,"<"&IFERROR(INDEX(a,s+1),9^9),$E:$E,"*Won")))
Dynamic array formulas.

Even on a short range I noticed a lag.

 

[Country_Calc_2]

Here's how to do it with whole column references:

david763.xlsx
	A	B	C	D	E	F	G	H
1	RFI Client	RFI Created Date	RFP Client	RFP Created Date	RFP Stage	Result	RFI Converted	RFI Won
2	Client A	1/1/2022	Client B	1/1/2022	Closed Lost	Client A	2	1
3	Client B	1/1/2024	Client A	1/1/2023	Closed Lost	Client B	0	0
4	Client C	1/1/2024	Client A	3/1/2024	Closed Won	Client C	0	0
5	Client D	1/1/2023	Client D	6/1/2023	Closed Lost	Client D	1	0
6	Client C	1/1/2023	Client C	1/1/2022	Closed Lost
Sheet4

Cell Formulas
Range	Formula
F2:F5	F2	=LET(a,UNIQUE(A:A),SORT(FILTER(a,(a<>"")*(a<>"RFI Client"))))
G2:G5	G2	=LET(a,SORT(FILTER($B:$B,$A:$A=$F2)),s,SEQUENCE(ROWS(a)),SUM(COUNTIFS($C:$C,$F2,$D:$D,">="&INDEX(a,s),$D:$D,"<"&IFERROR(INDEX(a,s+1),9^9))))
H2:H5	H2	=LET(a,SORT(FILTER($B:$B,$A:$A=$F2)),s,SEQUENCE(ROWS(a)),SUM(COUNTIFS($C:$C,$F2,$D:$D,">="&INDEX(a,s),$D:$D,"<"&IFERROR(INDEX(a,s+1),9^9),$E:$E,"*Won")))
Dynamic array formulas.

Even on a short range I noticed a lag.

Ok thanks. I was trying to make it more time efficient to update new data, but it could result in crashes if you have other applications open.

Fixed range is the way to go, but for other users who want to use reference entire column - you still need to fill rows with dummy data so that shorter report is equal to longer.