SOLVED

# Pairing data when having multiple different values in the same row

Copper Contributor

# Pairing data when having multiple different values in the same row

Hello dear community,
I'm having a problem with a new report where I'm trying to pair payroll codes with their number of hours per personnel number. The regular and overtime hours are always in the same columns, so no problem there, but the others extra codes appears one after the other on the same row associated with the personnel number and it's not always the same.
I'm trying to recreate a simpler table in order to have the hours of the payroll codes by personnel number.

The report with raw data

Where I would like to have the results

2 Replies
best response confirmed by ValeryeB (Copper Contributor)
Solution

# Re: Pairing data when having multiple different values in the same row

=SUMPRODUCT(IF((\$B\$2:\$H\$12=B\$14)*(\$A\$2:\$A\$12=\$A15),\$C\$2:\$I\$12))

You can apply this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021. The formula is in cell B15 and filled across range B15:I25.

# Re: Pairing data when having multiple different values in the same row

Mister Oliver, thank you, you are a genius.

Cheers from Montreal

1 best response

Accepted Solutions
best response confirmed by ValeryeB (Copper Contributor)
Solution

# Re: Pairing data when having multiple different values in the same row

=SUMPRODUCT(IF((\$B\$2:\$H\$12=B\$14)*(\$A\$2:\$A\$12=\$A15),\$C\$2:\$I\$12))

You can apply this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021. The formula is in cell B15 and filled across range B15:I25.