Trying to create spreadsheet that uses 3 different criteria to return a value

**Sugarbox** · 05-21-2022, 12:11 PM

Hello,

I work for a fire dept that allows us to put in for stations based off seniority and then class rank if seniority date is the same. We are able to pick up to 6 spots. If my first choice is selected by someone with more seniority, then it will look at my 2nd choice. I am able to download the entire transfer request as soon as the transfer is closed. I want to be able to copy and paste the transfer into an excel spreadsheet and have it fill in the results for me. Also, some stations might have multiple spots open. I sort the list by seniority date and then class rank. Right now I have to manually go through the list and then delete the stations as I go to do it. For instance:

Payroll Station Shift Vehicle Choice Available Stations
12 38 A Apparatus 1 38 A Apparatus
12 38 C Apparatus 2 38 C Apparatus
12 62 C Apparatus 3 38 C Apparatus
12 38 D Apparatus 4 38 D Apparatus
13 38 A Apparatus 1 62 C Apparatus
13 38 C Apparatus 2
13 62 C Apparatus 3
13 38 D Apparatus 4
14 38 A Apparatus 1
14 38 C Apparatus 2
14 62 C Apparatus 3
14 38 D Apparatus 4
15 38 A Apparatus 1
15 38 C Apparatus 2
15 62 C Apparatus 3
15 38 D Apparatus 4

Thank you for the help.

**AliGW** · 05-21-2022, 12:28 PM

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

**Sugarbox** · 05-21-2022, 04:47 PM

Thanks AliGW,

Please see attached sample, at least I think I did it right.

The data on the left side of the screen are the choices the firefighters put in for with their seniority date and class rank. I have them in order of seniority date and class rank. The info at the top right are the available stations the firefighters can choose from. The bottom right column is was the result of the posting should be with the correct formula.

**JeteMc** · 05-26-2022, 06:09 PM

I am confused as to why payroll 13 was given 38 C when the first choice (cell G8) appears to be 62 C which is open and not selected by payroll 12.
Also confused that payroll 13 has two class ranks (6 and 48).

**Sugarbox** · 05-30-2022, 08:08 PM

Hey JeteMc,

You are correct. I messed up the sample I attached. Sorry about that. I have corrected the spreadsheet and attached. Hopefully when looking at now it makes more sense. All payrolls should have same seniority date but each one has a different class rank. Each payroll should have only 4 choices shown.

**JeteMc** · 05-31-2022, 09:54 AM

This proposal employs helper columns (K:O) which may be moved and/or hidden for aesthetic purposes.
1. The column headers displaying station and shift are populated using: =INDEX($S3:$S7,COLUMNS($K1:K1))
2. Rows 4:19 calculate the assignments using: =AND(K$1=$C4,K$2=$D4,SUM(K$3:K3)=0,SUM($J4:J4)=0,SUMPRODUCT(($K$3:$O3)*($A$3:$A3=$A4))=0)+0
3. The results are given in T18:U21 using: =INDEX(C$4:C$19,AGGREGATE(15,6,(ROW(C$4:C$19)-ROW(C$3))/($A$4:$A$19=$S18)/($K$4:$O$19=1),1))
Let us know if you have any questions.

Trying to create spreadsheet that uses 3 different criteria to return a value

LinkBack

Thread Tools

Rate This Thread

Display

Trying to create spreadsheet that uses 3 different criteria to return a value

Re: Trying to create spreadsheet that uses 3 different criteria to return a value

Re: Trying to create spreadsheet that uses 3 different criteria to return a value

Re: Trying to create spreadsheet that uses 3 different criteria to return a value

Re: Trying to create spreadsheet that uses 3 different criteria to return a value

Re: Trying to create spreadsheet that uses 3 different criteria to return a value

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Return Values from Spreadsheet based on 2 criteria

VBA to send data from excel spreadsheet to a website form and return result to spreadsheet

[SOLVED] Macro- Search multiple criteria in spreadsheet return with multiple results in new sheet

Create possible HLOOKUP, VLOOKUP, INDEX, MATCH to search 2 criteria and return data

Need formula to return a value in one column when a criteria is create a list

We need to create a quoting spreadsheet using the following criteria:

I need help to create a network-ready spreadsheet that exports data to one spreadsheet!

Bookmarks

Bookmarks

Posting Permissions