Search code examples
excelexcel-formulaauto-generate

An advanced formula for getting serial nos automatically in excel

Can you help me write a formula starting from A4 cell to A20 cell for the serial no of the students to get a result like the serials in this photo. conditions:

  1. First non empty row will get the serial 1 and then increasing by 1
  2. After that it will check the columns B and D. Identical columns get same serial no. for that rows.
  3. For an empty row serial no will skip to the next row. Please check the photo and help me.
=IF(AND(B5<>"", D5<>""), IF(COUNTIFS(B$4:B5, B5, D$4:D5, D5) > 1, MATCH(B5&D5, B$4:B5&D$4:D5, 0), ROW()-4), "")

It is not working if first row is empty.

enter image description here

Solution

  • This solution does not use LET, so will work in older versions of Excel. However, in some of them you may need to enter it as an Array Formula (i.e. with Ctrl+Shift+Enter)

    =IF(COUNTA($B4:$E4)>0, IFERROR(INDEX($A$3:$A3, MATCH(1, --($B$3:$B3=$B4)*--($D$3:$D3=$D4), 0)), IFERROR(MAX($A$3:$A3), 0)+1), "")

    Breaking it down:

    IF(COUNTA($B4:$E4)>0, […], "")

    This will skip empty rows. Otherwise, it will look for a Serial Number.

    IFERROR(INDEX($A$3:$A3, MATCH(1, --($B$3:$B3=$B4)*--($D$3:$D3=$D4), 0)), […])

    This will check Columns B and D, from row 3 until the previous row, and check if any of those rows contain the same Name and DoB as the current row. If so, it will return the Serial Number from that row. If not…

    IFERROR(MAX($A$3:$A3), 0)+1

    It will find the largest Serial Number that has already been allocated, and add 1 to that.