Background/Business Case

I'm a recruiter who uses spreadsheets to organize the work I've done with candidates with respect to a certain job vacancy.

Like streak, I would like to have a pipeline that shows how my candidates are going through a certain progression or pipeline. I've put the different statuses of the pipeline here:

enter image description here

In the following figure, you can see that I've entered my candidates data, and I've put conditional formatting so that they are color depending on the status they're in: enter image description here


Notice how in this screenshot, i sorted the candidates according to the status alphabetically:

enter image description here However, i want it sorted according to the same order available in the pipeline status depicted above, ie according to this order: - initial contact - interested - discussing - ..

any ideas?


Assuming that you have the data in Sheet1 and the pipeline list in Sheet2, you can copy the data from Sheet1 to Sheet3. Do the editing in Sheet1, and any other sorting, such as by country, name, source, etc. and keep Sheet3 as an automatically updated, and sorted view based on the pipeline.

  • Create Sheet3 with the needed column headings to match those on Sheet1.
  • In Sheet3 cell A2 enter the formula: =ArrayFormula(sort(Sheet1!A2:E,match(left(Sheet1!D2:D,6),left(Sheet2!A1:A10,6),0),1))

You will need to adjust the following:

  • range A2:E by replacing E with your last column
  • range D2:D by replacing D with the column that actually has the status you are sorting on
  • the 6 in both left functions with the minimum number of characters to make each item in the pipeline unique.
  • the names of Sheet1 and Sheet2 if you have renamed the sheets to something more meaningful (as you probably should)

Adding and rearranging the items in the pipeline, as well as adding and editing rows on the main sheet, and inserting columns on the main sheet, are automatically accommodated by Google Sheets, so that you don't have to reformulate the equation for changes that you make in the pipeline, or for new entries in the data. (One exception to this: if you add a column to Sheet1 you will need to change the header row in Sheet3 to match.)

IMHO, as complex as this seems to be getting, you probably ought to look into switching to a database for this instead of a spreadsheet.

| improve this answer | |
  • hey man great answer.. i wanted to follow the logic of your equation though and left you a question on the other answer you provided here please take a look – abbood Jul 10 '17 at 6:32
  • @abbood That other answer isn't mine, so I was never notified of the comment. But, in that answer, what's happening is that the {"sop";"con";"ten";"bas"} portion is creating a "temporary" range to match against using the first 3 characters of the voice range. A similar approach could have been done here, rather than the range on the other sheet. That, however would either lock you into only those stages in the pipeline, or be tricky to edit as stages are added. This version scales better, that one don't, and don't need to since choir parts have been fixed for generations now. – user144900 Jul 10 '17 at 6:39
  • here is a similar other question – abbood Aug 30 '17 at 6:34

Not the answer you're looking for? Browse other questions tagged or ask your own question.