Macro creation

[Duckbill32]

Hi there!
I am trying to come up with a macro, what would deduct cell values from the certain cells, depending on their existence.
In the example before, one can see data in the D column for the for the data points "1" and "2" in the C column. Those data points belong to the specific file, named in the B column (they are not necessarily named "Image1" etc.). There are always two data points "1" and "2" for one file name.
I am trying to build a macro that would calculate the difference between the data point and write it in the G column. In the example below, one can see G3=D4-D3, similarly G5=D5-D4 etc. The though part, however, is that the amount of files (not data points) may vary. On the screenshot, there are three files, but there could be one or hundred.
Therefore, I am trying to come up with some sort of IF function inside the macro, that would first analyze how many lines with values there are in D column from D3 until blank cell (D9 in the example below). Then, it would deduct the second data point value from the first one for each file name and write the results in the G column. It could also put the file name in front of the result, for example in the F column, e.g. Image 1 = -2.54E-08.

Any help would ne much appreciated

Thank you excels, macros and VBAs professionals!

 

[Legacy 143009]

Do the values always exist in couples? What I mean is are there only single ones and twos for the first image, single ones and twos for the second image and etc..?

Are the images always in order or can they be in any order? What I mean is does the second datapoint for the image2 always come after the first point of image2?

 

[Duckbill32]

Do the values always exist in couples? What I mean is are there only single ones and twos for the first image, single ones and twos for the second image and etc..?

Are the images always in order or can they be in any order? What I mean is does the second datapoint for the image2 always come after the first point of image2?

Hi,
indeed, so far, the values always existed in couples - single ones and twos for each file (not named as “image” but as some rather random sample name).
The answer for the second question is yes again, so far all the datapoints came exactly like you described.

 

[bebo021999]

Why macro? formula can do it easily.
In G3:

=IF(C3<>1,"",SUMIFS($D$3:$D$9,$B$3:$B$9,$B3,$C$3:$C$9,2)-SUMIFS($D$3:$D$9,$B$3:$B$9,$B3,$C$3:$C$9,1)

In F3:

=IF(C3=1,B3,"")

 

[Legacy 143009]

This code will work for you no matter in which order the filenames or datapoints are:

Sub test()
  Dim d As Object, c As Variant, i As Long, lr As Long
  Dim images() As Variant
  Set d = CreateObject("Scripting.Dictionary")

  lr = Range("B2").End(xlDown).Row
  c = Range("B3:B" & lr)
  For i = 1 To UBound(c, 1)
    d(c(i, 1)) = 1
  Next i
  images = Application.Transpose(d.keys)

  For Each imageName In images
    For r = 3 To lr
      If Cells(r, 2).Value = imageName And Cells(r, 3).Value = 1 Then
        Cells(r, 6).Value = imageName
        For i = r To lr
          If Cells(i, 2).Value = imageName And Cells(i, 3).Value = 2 Then
            Cells(r, 7).Value = Cells(i, 4).Value - Cells(r, 4).Value
          End If
        Next
      End If
    Next
  Next
End Sub

Note: This code won't work if you have more than 1 ones or twos for the same filename.

 

[Duckbill32]

Why macro? formula can do it easily.
In G3:

=IF(C3<>1,"",SUMIFS($D$3:$D$9,$B$3:$B$9,$B3,$C$3:$C$9,2)-SUMIFS($D$3:$D$9,$B$3:$B$9,$B3,$C$3:$C$9,1)

In F3:

=IF(C3=1,B3,"")

I am searching for a more sustainable solution, that would now require any kind of manual insert. I would, of course, make macro that is inserting those formulas in these cells, but VBA or macro I could just run always is much more sustainable in my case. Thank you for the suggestion, though!!

 

[Duckbill32]

This code will work for you no matter in which order the filenames or datapoints are:

Sub test()
  Dim d As Object, c As Variant, i As Long, lr As Long
  Dim images() As Variant
  Set d = CreateObject("Scripting.Dictionary")

  lr = Range("B2").End(xlDown).Row
  c = Range("B3:B" & lr)
  For i = 1 To UBound(c, 1)
    d(c(i, 1)) = 1
  Next i
  images = Application.Transpose(d.keys)

  For Each imageName In images
    For r = 3 To lr
      If Cells(r, 2).Value = imageName And Cells(r, 3).Value = 1 Then
        Cells(r, 6).Value = imageName
        For i = r To lr
          If Cells(i, 2).Value = imageName And Cells(i, 3).Value = 2 Then
            Cells(r, 7).Value = Cells(i, 4).Value - Cells(r, 4).Value
          End If
        Next
      End If
    Next
  Next
End Sub

Note: This code won't work if you have more than 1 ones or twos for the same filename.

Thank you very much @Flashbond! It works fantastically!
So far I was dealing with only two data points corresponding to two peaks on the gaussian plot, so no more data points should appear in the excel
Once again, thank you very much for the help!

 

[Legacy 143009]

Glad it did help

So far I was dealing with only two data points corresponding to two peaks on the gaussian plot, so no more data points should appear in the excel
Once again, thank you very much for the help!

Based on this information this could be slightly faster:

Sub test()
  Dim d As Object, c As Variant, i As Long, lr As Long
  Dim images() As Variant
  Set d = CreateObject("Scripting.Dictionary")

  lr = Range("B2").End(xlDown).Row
  c = Range("B3:B" & lr)
  For i = 1 To UBound(c, 1)
    d(c(i, 1)) = 1
  Next i
  images = Application.Transpose(d.keys)

  For Each imageName In images
    For r = 3 To lr
      If Cells(r, 2).Value = imageName And Cells(r, 3).Value = 1 Then
        Cells(r, 6).Value = imageName
        For i = r To lr
          If Cells(i, 2).Value = imageName And Cells(i, 3).Value = 2 Then
            Cells(r, 7).Value = Cells(i, 4).Value - Cells(r, 4).Value
            Exit For
          End If
          Exit For
        Next
      End If
    Next
  Next
End Sub