Table Compare and Data Sources

[david gabra]

Hi
Please can you help me show the values by comparing 2 tables. A standard vlookup will not help (it will only show the top line) - limitation.

I would like please 3 outputs.
- the number of lines that appear for the same Job - comparing A to B - (Job a primary key)
- the sum total of the lines
- and lastly the actual values that are make up the total.

I have no idea how to do this - but this will be magic if someone can help me - wilth excel of vba skills- beyond my limits.

Thank you in advance!!!!!!! if possible of course.

 

[GraH - Guido]

would this do the job for you?

in J2: =COUNTIF($F$4:$F$9,$I4), copy down
in K2: =SUMIF($F$4:$F$9,I4,$G$4:$G$9), copy down
in L2: = =IF(COLUMNS($L$3:L$3)>$J4,"",AGGREGATE(14,6,($G$4:$G$9)/($F$4:$F$9=$I4),COLUMNS($L$3:L$3))), copy down and to the side (you can add columns if a job reference appears more then twice in the table.

 

[david gabra]

that is brilliant

 

[neeraj2050]

Dear Guido Sir,

Could you please explain this formula

=IF(COLUMNS($L$3:L$3)>$J4,"",AGGREGATE(14,6,($G$4:$G$9)/($F$4:$F$9=$I4),COLUMNS($L$3:L$3)))

 

[GraH - Guido]

=IF(COLUMNS($L$3:L$3)>$J4,"",AGGREGATE(14,6,($G$4:$G$9)/($F$4:$F$9=$I4),COLUMNS($L$3:L$3)))

• The IF ( ) first checks the number of columns starting from L3. So in column L this is 1, in column K this is 2 and so on. This part is done with this argument COLUMNS($L$3:L$3). In cell M4 this would be COLUMNS($L$3:M$3) and it returns 3, the number of columns in the range L->M.
  
• If this number of columns is larger then the number in J4, which is the number of times a job value is repeated, it returns a blank.
• If this number is smaller, you want to find the individual values. Therefore AGGREGATE is used.
  
  • 14 is the function number for large. Basically for finding the biggest number in a range.
  • 6 is the option to ignore error values. I'm forcing these errors with the next part of the formula: ($G$4:$G$9)/($F$4:$F$9=$I4).
  • This is actually an array calculation. Simply put it divides each value in the range ($G$4:$G$9) by each value in the range ($F$4:$F$9=$I4). But what is this doing?
    
  • It verifies if the values in the range ($F$4:$F$9) equal the value =$I4. So it actually returns a boolean TRUE or FALSE. If you use these in arithmetic's in Excel they are converted to 1 and 0.
  • It actually sees this: {2;3;4;4;2;3}/{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE}
  • And returns this: {2;#DIV/0!;4;#DIV/0!;#DIV/0!;#DIV/0!}
  • Remember option 6: it ignores the errors thus seeing only this array {2;4}
  • To fetch all the values from this array I re-use the column logic in the last argument of the AGGREGATE function, being [k].
    
  • [k] = 1 in the cell L4; 2 in the cell K4; 3 in M4, and so on.
  • Function 14: it will first return the largest value, then the second largest, the third large and so on.

What if there are 2 equal values? AGGREGATE can handle these as well and will repeat those values.

 

[neeraj2050]

@Guido Sir: Great way of explaining, so minutely. Though I understood except for following part

• To fetch all the values from this array I re-use the column logic in the last argument of the AGGREGATE function, being [k].
• [k] = 1 in the cell L4; 2 in the cell K4; 3 in M4, and so on.

Would you mind to elaborate it a little further

 

[GraH - Guido]

This is the syntax for the array type function =AGGREGATE(function_num;options;array;k), where [k] is an optional argument.
Understand [k] as the n-th position in the returned array.

These are also great reference...
https://chandoo.org/wp/tag/aggregate/
https://chandoo.org/wp/2013/05/17/sum-of-top-3-values-meeting-criteria/

PS: My pleasure..., neeraj2050, sometimes the teacher in me awakens

 

[david gabra]

Brilliant