extract data for low prices based on date(today) between two sheets

[Mussala]

Hello
I need macro if it's possible because I have big data for each sheet .
I want to match sheet DECREASE for column D with IN sheet for column D ,if the ID is matched , then should search low price in column G in DECREASE sheet based on DATE(TODAY) ,then should create report as I did it in DIFFERENCES sheet.
should subtract column G in DECREASE sheet from column G in IN sheet and the column H=F*G as I did it by formulas without showing formulas .
should update DIFFERENCES sheet. when update DECREASE sheet .
just search for low price in column G in DECREASE sheet with comparison INV sheet .

df
	C	D	E	F	G	H
1	ITEM	ID	ORDER NO	QTY	PRICE	AMOUNT
2	1	ATTR001	OR00	10	100	1000
3	2	ATTR002	OR00	10	120	1200
4	3	ATTR003	OR00	10	130	1300
5	4	ATTR004	OR00	10	140	1400
IN

df
	A	B	C	D	E	F	G	H
1	DATE	NAME	INVOICE NO	ID	ORDER NO	QTY	PRICE	AMOUNT
2	01/06/2023	Mussaila1	INA00	ATTR001	OR00	10	100	1000
3	01/06/2023	Mussaila1	INA00	ATTR002	OR00	10	120	1200
4	01/06/2023	Mussaila1	INA00	ATTR003	OR00	10	130	1300
5	01/06/2023	Mussaila1	INA00	ATTR004	OR00	10	140	1400
6	02/06/2023	Mussaila2	INA01	ATTR001	OR01	5	90	450
7	02/06/2023	Mussaila2	INA01	ATTR002	OR02	10	100	1000
8	02/06/2023	Mussaila1	INA02	ATTR004	OR00	10	140	1400
DECREASE

before

df
	A	B	C	D	E	F	G	H
1	DATE	NAME	INVOICE NO	ID	ORDER NO	QTY	PRICE	AMOUNT
2
3
DIFFERENCES

expected result

df
	A	B	C	D	E	F	G	H
2	02/06/2023	Mussaila2	INA01	ATTR001	OR01	5	-10	-50
3	02/06/2023	Mussaila2	INA02	ATTR002	OR02	10	-20	-200
DIFFERENCES

Cell Formulas
Range	Formula
G2:G3	G2	=DECREASE!G6-IN!G2
H2	H2	=F2*G2
H3	H3	=G3*F3

 

[DanteAmor]

Hi @Mussala

It seems to me that there are very few examples that you are putting.

I need you to help me with several doubts that I have.

1. In the "IN" sheet are the IDs unique?

,if the ID is matched ,

------------------​

2. What do you mean by "search low price in column G in DECREASE sheet"?

then should search low price in column G in DECREASE sheet based on DATE(TODAY)

Is the lower price or the date (Today) considered?
This is where you should put more examples, if there are several records with the same ID and the same date, let me explain, then I need you to put those examples and what is the expected result.

------------------​

3. Where do you want the new records in the "DIFFERENCES" sheet, under the existing ones, or do you have to delete the records and put the new ones?

should update DIFFERENCES sheet. when update DECREASE sheet .

------------------​

4. After updating the "DECREASE" sheet run the macro, you can put the macro in a button. That is, you press the button and the macro will read all the records from the "DECREASE" sheet and update the "DIFFERENCES" sheet. Do you agree or what is your idea?

when update DECREASE sheet .

------------------​

5. In your results in cell C3 it says "INA02" it should say "INA01".
Is it a typo or why did the data change?

------------------​

6. In the "DECREASE" sheet you have the ID "ATTR004" with Today's date and it also exists in the "IN" sheet but you didn't put the record in the results, why?

------------------​

I understand that there are more questions than solutions, but without that information the macro would not work.

Now assuming many things, try the following macro I hope my assumptions are correct

Sub extract_data()
  Dim a As Variant, b As Variant, c As Variant
  Dim dic As Object
  Dim i As Long, j As Long, k As Long
 
  Set dic = CreateObject("Scripting.Dictionary")
 
  a = Sheets("IN").Range("A1", Sheets("IN").Range("H" & Rows.Count).End(3)).Value
  b = Sheets("DECREASE").Range("A1", Sheets("DECREASE").Range("H" & Rows.Count).End(3)).Value
  ReDim c(1 To UBound(a, 1), 1 To UBound(a, 2))
 
  For i = 2 To UBound(a)
    dic(a(i, 4)) = a(i, 7)
  Next
 
  For i = 2 To UBound(b)
    If b(i, 1) = Date Then
      If dic.exists(b(i, 4)) Then
        If b(i, 7) - dic(b(i, 4)) <> 0 Then
          k = k + 1
          For j = 1 To 6
            c(k, j) = b(i, j)
          Next
          c(k, 7) = b(i, 7) - dic(b(i, 4))
          c(k, 8) = b(i, 6) * c(k, 7)
        End If
      End If
    End If
  Next
 
  With Sheets("DIFFERENCES")
    .Range("A2:H" & Rows.Count).ClearContents
    .Range("A2").Resize(k, 8).Value = c
  End With
End Sub

------------------​

If you want to put the new records below the existing ones, then change these lines:

  With Sheets("DIFFERENCES")
    .Range("A2:H" & Rows.Count).ClearContents
    .Range("A2").Resize(k, 8).Value = c
  End With

By these lines:

  With Sheets("DIFFERENCES")
    .Range("A" & .Range("A" & Rows.Count).End(3).Row + 1).Resize(k, 8).Value = c
  End With

If the macro does not work as you want, it is due to the doubts that I have. In this case, please you must respond promptly to the 6 points with examples.

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[Mussala]

1. In the "IN" sheet are the IDs unique?

I'm not sure what you ask for it, but the ID is in column D in IN sheet and match with column D for DECREASE sheet. I suppose is unique based on my data .

2. What do you mean by "search low price in column G in DECREASE sheet"?

so to know low price in DECREASE sheet depends on comparison with IN sheet but should search today(date) in DECREASE sheet and after that should match with IN sheet.
example :
let's say date today:03/06/2023 and the ID: ATTR001 and the price:90 in DECREASE sheet and price for the same ID : 100 in IN sheet, so directly will show ID for DECREASE sheet into DIFFERENCES sheet and calculation as I did it.

Is the lower price or the date (Today) considered?

just I want low price for entering data on date (TODAY) otherwise should ignore it.

This is where you should put more examples, if there are several records with the same ID and the same date, let me explain, then I need you to put those examples and what is the expected result.

no this case doesn't occurs at all( means ID doesn't repeat for date(today)

3. Where do you want the new records in the "DIFFERENCES" sheet, under the existing ones, or do you have to delete the records and put the new ones?

I'm not sure if I understand this, but I supposes if there is new record , then should put under the existing old records without repeat copying records have already existed

4. After updating the "DECREASE" sheet run the macro, you can put the macro in a button. That is, you press the button and the macro will read all the records from the "DECREASE" sheet and update the "DIFFERENCES" sheet. Do you agree or what is your idea?

yes I agree.

5. In your results in cell C3 it says "INA02" it should say "INA01".
Is it a typo or why did the data change?

sorry ! this is typo , should be INA01.

6. In the "DECREASE" sheet you have the ID "ATTR004" with Today's date and it also exists in the "IN" sheet but you didn't put the record in the results, why?

that's because the same price, I don't need it , just search for low price .
by the way I test your main macro and gives application defined error in this line

  .Range("A2").Resize(k, 8).Value = c

I hope theses answering help you .

 

[DanteAmor]

I supposes if there is new record , then should put under the existing old records

Please, before executing the macro, check that there are records 1. with today's date (June 3, 2023), 2. that the ID exists on both sheets. 3. That the price difference is different from 0.


And one more favor, I continue, without understanding this part.
If you could explain it with several examples, to understand when it is and when it is not.

so to know low price in DECREASE sheet depends on comparison with IN sheet but should search today(date) in DECREASE sheet and after that should match with IN sheet.

------------------------------------​

The following macro works with the example records you provided.
Try this:

Sub extract_data()
  Dim a As Variant, b As Variant, c As Variant
  Dim dic As Object
  Dim i As Long, j As Long, k As Long
 
  Set dic = CreateObject("Scripting.Dictionary")
 
  a = Sheets("IN").Range("A1", Sheets("IN").Range("H" & Rows.Count).End(3)).Value
  b = Sheets("DECREASE").Range("A1", Sheets("DECREASE").Range("H" & Rows.Count).End(3)).Value
  ReDim c(1 To UBound(a, 1), 1 To UBound(a, 2))
 
  For i = 2 To UBound(a)
    dic(a(i, 4)) = a(i, 7)
  Next
 
  For i = 2 To UBound(b)
    If b(i, 1) = Date Then
      If dic.exists(b(i, 4)) Then
        If b(i, 7) - dic(b(i, 4)) <> 0 Then
          k = k + 1
          For j = 1 To 6
            c(k, j) = b(i, j)
          Next
          c(k, 7) = b(i, 7) - dic(b(i, 4))
          c(k, 8) = b(i, 6) * c(k, 7)
        End If
      End If
    End If
  Next
 
  If k = 0 Then
    MsgBox "No records match"
  Else
    With Sheets("DIFFERENCES")
      .Range("A" & .Range("A" & Rows.Count).End(3).Row + 1).Resize(k, 8).Value = c
    End With
  End If
End Sub

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

 

[Mussala]

ok
the macro is in post#2 is perfect and this is what I want , but I no know if you can help to avoid error.

Please, before executing the macro, check that there are records 1. with today's date (June 3, 2023), 2. that the ID exists on both sheets. 3. That the price difference is different from 0.

what I think when happens theses cases , then should show message " there is no date(today) then there is no matching" or " there is no low price , so there is no matching" or " there is missed ID" .

 

[DanteAmor]

what I think when happens theses cases , then should show message " there is no date(today) then there is no matching" or " there is no low price , so there is no matching" or " there is missed ID" .

Try the macro in post #4 already contains the message if there are no matches.

 

[Mussala]

thanks very much for your time & solution .