Highest & lowest value for multiple columbs

[Ananthak275]

need to create a VBA code that gets the difference between the highest and lowest value for each name in a new tab (lets call this new sheet for the output “output or difference”

Input:

Name	apple	Trees
A	1	2
A	2	6
A	4	7
A	6	2
B	3	5
C	1	3
C	3	6
C	6	7

name	Apple	Trees
A	5	5
B	3	5
C	3	1

 

[alansidman]

Book1
	A	B	C	D	E	F	G
1	Name	apple	Trees		Name	Diff Apples	Diff Trees
2	A	1	2		A	5	5
3	A	2	6		B	0	0
4	A	4	7		C	5	4
5	A	6	2
6	B	3	5
7	C	1	3
8	C	3	6
9	C	6	7
Sheet1

Power Query Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"Max Apple", each List.Max([apple]), type number}, {"Min Apple", each List.Min([apple]), type number}, {"Max Trees", each List.Max([Trees]), type number}, {"Min Trees", each List.Min([Trees]), type number}}),
    #"Inserted Subtraction" = Table.AddColumn(#"Grouped Rows", "Subtraction", each [Max Apple] - [Min Apple], type number),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Subtraction",{{"Subtraction", "Diff Apples"}}),
    #"Inserted Subtraction1" = Table.AddColumn(#"Renamed Columns", "Subtraction", each [Max Trees] - [Min Trees], type number),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Subtraction1",{{"Subtraction", "Diff Trees"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Max Apple", "Min Apple", "Max Trees", "Min Trees"})
in
    #"Removed Columns"

 

[Ananthak275]

Hey I’m sorry, I need this to be a VBA code

 

[alansidman]

OK. Good Luck.

 

[Joe4]

Can you explain how you arrive at your results for values B and C?
If I subtract the Lowest value fromt he Highest Value, I get the results shown by alansidman, not yours.

For value B, since there is only one record, it would seem that the Highest and Lowest values are exactly the same, so anything minus itself is 0.

And column value C, we have:

Apple: Highest (6) - Lowest (1) = 5
So I am not sure how you arrive at 3.

Likewise, we have:
Trees: Highest (7) - Lowest (3) = 4
So I am not sure how you arrive at 1.

And are you still really using Excel 2013?
I think this may be much easier in the newer versions of Excel that have the MAXIFS and MINIFS functions.

 

[Ananthak275]

Can you explain how you arrive at your results for values B and C?
If I subtract the Lowest value fromt he Highest Value, I get the results shown by alansidman, not yours.

For value B, since there is only one record, it would seem that the Highest and Lowest values are exactly the same, so anything minus itself is 0.

And column value C, we have:

Apple: Highest (6) - Lowest (1) = 5
So I am not sure how you arrive at 3.

Likewise, we have:
Trees: Highest (7) - Lowest (3) = 4
So I am not sure how you arrive at 1.

If the value only has one row, it doesn’t substract from anything. Keep the value as it is. As seen in Name=B.

Oops sorry. Yes my calculation is off. Here is my output

Name	apple	Trees
A	5	5
B	3	5
C	5	4

 

[Joe4]

OK, assuming that you have two sheets:
Input: this is where your original data currently resides
Output: this is where you want the output to go
and you data starts in cell A1, so your Input sheet looks like this:

You can use this VBA code:

Sub MySummary()

    Dim wsIn As Worksheet
    Dim wsOut As Worksheet
    Dim lr As Long
    Dim lc As Long
    
    Application.ScreenUpdating = False
    
'   Designate Input and Output worksheets
    Set wsIn = Sheets("Input")
    Set wsOut = Sheets("Output")
    
'   Copy header row from Input Sheet to Output sheet
    wsIn.Range("1:1").Copy wsOut.Range("A1")
    
'   Copy first column from Input Sheet to Output sheet
    wsIn.Range("A:A").Copy wsOut.Range("A1")
    
'   Filter duplicates out of column A
    wsOut.Columns("A:A").RemoveDuplicates Columns:=1, Header:=xlYes
    
'   Find last row and column on Output sheet
    lr = wsOut.Cells(wsOut.Rows.Count, "A").End(xlUp).Row
    lc = wsOut.Cells(1, wsOut.Columns.Count).End(xlToLeft).Column
    
'   Populate formulas
    wsOut.Range(wsOut.Cells(2, "B"), wsOut.Cells(lr, lc)).Formula2R1C1 = _
        "=MAX(IF(Input!C1=Output!RC1,Input!C))-IF(COUNTIF(Input!C1,Output!RC1)>1,MIN(IF(Input!C1=Output!RC1,Input!C)),0)"
    
    Application.ScreenUpdating = True
    
    MsgBox "Macro Complete!", vbOKOnly
    
End Sub

which should result in this on the Output sheet:

 

[Ananthak275]

OK, assuming that you have two sheets:
Input: this is where your original data currently resides
Output: this is where you want the output to go
and you data starts in cell A1, so your Input sheet looks like this:
View attachment 91725

You can use this VBA code:

Sub MySummary()

    Dim wsIn As Worksheet
    Dim wsOut As Worksheet
    Dim lr As Long
    Dim lc As Long
   
    Application.ScreenUpdating = False
   
'   Designate Input and Output worksheets
    Set wsIn = Sheets("Input")
    Set wsOut = Sheets("Output")
   
'   Copy header row from Input Sheet to Output sheet
    wsIn.Range("1:1").Copy wsOut.Range("A1")
   
'   Copy first column from Input Sheet to Output sheet
    wsIn.Range("A:A").Copy wsOut.Range("A1")
   
'   Filter duplicates out of column A
    wsOut.Columns("A:A").RemoveDuplicates Columns:=1, Header:=xlYes
   
'   Find last row and column on Output sheet
    lr = wsOut.Cells(wsOut.Rows.Count, "A").End(xlUp).Row
    lc = wsOut.Cells(1, wsOut.Columns.Count).End(xlToLeft).Column
   
'   Populate formulas
    wsOut.Range(wsOut.Cells(2, "B"), wsOut.Cells(lr, lc)).Formula2R1C1 = _
        "=MAX(IF(Input!C1=Output!RC1,Input!C))-IF(COUNTIF(Input!C1,Output!RC1)>1,MIN(IF(Input!C1=Output!RC1,Input!C)),0)"
   
    Application.ScreenUpdating = True
   
    MsgBox "Macro Complete!", vbOKOnly
   
End Sub

which should result in this on the Output sheet:
View attachment 91727

Hi yes this works! but
1. can you make it so that in case Name doesn't exist in the first column, it would still work. For example, Name exists in Column D or F.
2. Also are you able to change the columns i calculate for? Sometimes Apple and Trees columns won't be right after one another. Locating this column

 

[Joe4]

Hi yes this works! but
1. can you make it so that in case Name doesn't exist in the first column, it would still work. For example, Name exists in Column D or F.
2. Also are you able to change the columns i calculate for? Sometimes Apple and Trees columns won't be right after one another. Locating this column

If you want code more tailored to your exact structure, then I need you to provide exact details instead of general ones.
I need to know exact ranges of where things exist.