Posts by falak

Quote from Mumps

To clarify:

You now want the Master file to display only the total quantity in column H for each order number instead of all the data. Is this correct?

Yes. I want all the multiple entries as a single entry conditioning that the ColumnH "Order Quantity" should having a summation of all the multiple entries. Thankyou for all the help

Quote from Mumps

Change the part in red.

Worked. Kindly look into this small problem post #44. I apologies for any inconvenience caused.

@Mumps

Hi I am stuck in a slight problem here what i want to do is, in the uploaded file "OTIC" there are number of entries as for example order number in ColumnG "9207116092" have 3-entries. What i want is to sort all the multiple number of entries as a single entry, that when its uploaded using macro "Upload Data" I want it to add all the data of "Order Quantity" in ColumnH and show as a single entry of "9207116092" having the total number of Order Quantities of all the entries, in this case "50,100,250, so instead of three entries i want a single entry of "9207116092" with Order Quantity as 400 for the consider example.

Code of Macro "Upload Data"

Sub upload_data()
 Dim WScopy As Worksheet, WSdest As Worksheet, desWB As Workbook, FileToOpen As Variant, cRow As Long, Lastrow As Long
 Set desWB = ThisWorkbook
 Set WSdest = desWB.Sheets(1)
 Application.ScreenUpdating = False
 FileToOpen = Application.GetOpenFilename(Title:="Browse for your file & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
 If FileToOpen = False Then Exit Sub
 Set OpenBook = Application.Workbooks.Open(FileToOpen)
 With Sheets(1)
 cRow = .Cells(Rows.Count, "A").End(xlUp).row
 .Range("A2:K" & cRow).copy
 With WSdest
 .Cells(WSdest.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
 Lastrow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
 .Range("M5:M" & Lastrow).Formula = "= I5/(H5 - L5)"
 End With
 End With
 ActiveWorkbook.Close False
 Application.CutCopyMode = False
 Application.ScreenUpdating = True
End Sub

Quote from Mumps

Place this macro in your Master file and run it. When prompted to open a file, open the POD file.

Code

Sub uploadPODdata()
    Dim WScopy As Worksheet, WSdest As Worksheet, desWB As Workbook, FileToOpen As Variant, RngList As Object, key As Variant
    Dim DRow As Long, cRow As Long, Lastrow As Long, fnd As Range, PO As Range, sAddr As String, arr As Variant, i As Long
    Set desWB = ThisWorkbook
    Set WSdest = desWB.Sheets(1)
    Lastrow = WSdest.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Application.ScreenUpdating = False
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your file & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
    If FileToOpen = False Then Exit Sub
    Set OpenBook = Application.Workbooks.Open(FileToOpen)
    With Sheets(1)
        DRow = .Cells(.Rows.Count, "C").End(xlUp).Row
        arr = .Range("C5:C" & DRow).Resize(, 2).Value
        Set RngList = CreateObject("Scripting.Dictionary")
        For i = LBound(arr) To UBound(arr)
            If Not RngList.Exists(arr(i, 1)) Then
                RngList.Add key:=arr(i, 1), Item:=arr(i, 2)
                With WSdest.Cells(4, 1).CurrentRegion
                    .AutoFilter 7, arr(i, 1)
                End With
                With WSdest
                    .Range("F5:F" & Lastrow).SpecialCells(xlCellTypeVisible) = RngList(arr(i, 1))
                End With
            End If
        Next i
        WSdest.Range("A4").AutoFilter
    End With
    With WSdest
        cRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("N4:N" & cRow).Formula = "=if(F4=E4,M4*1,M4*0)"
    End With
    Application.ScreenUpdating = True
End Sub

Display More

Worked!! If i want to mend the code in a way that i want if F4 is greater than or equal to E4 then M4*1 else M4*0 so is my code correct?

.Range("N4:N" & cRow).Formula = "=if(F4<=E4,M4*1,M4*0)"

Quote from Mumps

In column C of the POD file, all the order numbers are the same. Column G of the Master file has multiple occurrences of each order number. For example, there are 11 occurrences of order number 9207116092 in both files. The POD file has 8 different dates in column D so I'm not sure which POD dates to use. Please clarify in detail.

Apologies if this annoys you, kindly look into the file attached now

Quote from Mumps

In column C of the POD file, all the order numbers are the same. Column G of the Master file has multiple occurrences of each order number. For example, there are 11 occurrences of order number 9207116092 in both files. The POD file has 8 different dates in column D so I'm not sure which POD dates to use. Please clarify in detail.

Also, as POD file is a user entry form if its possible that one entry for a PO number "9207116092" is enough and it automatically paste that POD to all the rows having "9207116092" in masterfile, like it can save human efforts which is my main goal. as for a specific PO number POD will be same regardless of the entries found in masterfile

Mumps Hi and thankyou for replying. Im stuck long on this, i have attached these two files so what i want to do its to upload data from the file "POD", i want each PO number to be matched with PO number mentioned in Mastrerfile and i want the POD of that respective PO number to be put in respective columnF

as i have multiple PO numbers of same entries and its a fact that for all same PO numbers the POD doesnot change.

The Error is it only copies to one cell of that respective PO numbers even thou it should be copuied to all the rows have that same PO nuber

Example: PO number in columnC in POD file 9207116092 C5 i want it to be matched and pasted to the row where PO number "9207116092" is found in Masterfile, now as the same PO numbers have 3entries i want the POD to be copied to all of these rows having same PO number "9207116092"

I wish you well, thankyou for all the efforts.

Note: Use the macro "Upload POD "to upload file POD

So i basically want to get PO matched from the file browse from computer and copied in a way that wherever PO "9207116092" in C4 of file POD is mentioned in Masterfile ColumG "PO" (like we dont have to right "9207116092" again and again just onetime is enough) and the POD ColumnD from file "POD" is copied to all the rows in CoulmnF(POD) in file "Mastefile" having the PO number as "9207116092"

Note: Use POD macro to upload POD file

Sub uploadPODdata()
 Dim WScopy As Worksheet, WSdest As Worksheet, desWB As Workbook, FileToOpen As Variant, DRow As Long, cRow As Long, lastRow As Long, fnd As Range, PO As Range
 Set desWB = ThisWorkbook
 Set WSdest = desWB.Sheets(1)
 Application.ScreenUpdating = False
 FileToOpen = Application.GetOpenFilename(Title:="Browse for your file & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
 If FileToOpen = False Then Exit Sub
 Set OpenBook = Application.Workbooks.Open(FileToOpen)
 With Sheets(1)
 DRow = .Cells(Rows.Count, "C").End(xlUp).row
 For Each PO In Range("C5:C" & DRow)
 Set fnd = WSdest.Range("G:G").Find(PO, LookIn:=xlValues, lookat:=xlWhole)
 
 

 If Not fnd Is Nothing Then
 fnd.Offset(, -1) = PO.Offset(, 1)
 End If
 Next PO
 End With
 With WSdest
 cRow = .Cells(.Rows.Count, "A").End(xlUp).row
 .Range("N4:N" & cRow).Formula = "=if(F4=E4,M4*1,M4*0)"
 End With
 ActiveWorkbook.Close False
 Application.CutCopyMode = False
 Application.ScreenUpdating = True
End Sub

So i basically want to get PO matched from the file browse from computer and copied in a way that wherever PO "9207116092" in C4 of file POD is mentioned in Masterfile ColumG "PO" (like we dont have to right "9207116092" again and again just onetime is enough and it paste the POD to all the rows having a PO "9207116092", it should paste the POD date (3rd Jan for this case ) there which is mentioned in D in file POD and ColumnF in Masterfile

NOTE: use the macro Upload POD to upload from the file "POD"

Sub uploadPODdata()
 Dim WScopy As Worksheet, WSdest As Worksheet, desWB As Workbook, FileToOpen As Variant, cRow As Long, lastRow As Long, fnd As Range, PO As Range
 Set desWB = ThisWorkbook
 Set WSdest = desWB.Sheets(1)
 Application.ScreenUpdating = False
 FileToOpen = Application.GetOpenFilename(Title:="Browse for your file & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
 If FileToOpen = False Then Exit Sub
 Set OpenBook = Application.Workbooks.Open(FileToOpen)
 With Sheets(1)
 cRow = .Cells(Rows.Count, "C").End(xlUp).row
 For Each PO In Range("C5:C" & cRow)
 Set fnd = WSdest.Range("G:G").Find(PO, LookIn:=xlValues, lookat:=xlWhole)
 If Not fnd Is Nothing Then
 fnd.Offset(, -1) = PO.Offset(, 1)
 End If
 Next PO
 End With
 With WSdest
 cRow = .Cells(.Rows.Count, "A").End(xlUp).row
 .Range("N4:N" & cRow).Formula = "=if(F4=E4,M4*1,M4*0)"
 End With
 ActiveWorkbook.Close False
 Application.CutCopyMode = False
 Application.ScreenUpdating = True
End Sub

@Mumps

As you helped for the same thing before kindly look into the files attached again

I just basically wants to update the data by matching its order numbers

-Uploading the data from browser and pasting it Masterfile attached below by button name "Upload POD"

- As both the workbooks have a same column name "Order number" so while pasting it should be pasted into the respective row where that "Order number" in the master file is location

-As for example In Masterfile C5 has Order number of "9207116092" so when uploading the document having Proof data occurs the value mentioned in Order number of "9207116092" in file Purchase should be paste corresponding row5 in Proof of delivery date" columnF , the value against order number 9207116092 is 3rd Jan

copying data from Purchase file, as an example Order number "9207116092" so it should search in Masterfile and paste the Proof of delivery date in COlumnG ("3rd Jan") for this example, in the row where Order number ""9207116092"" is located or Found i.e in C5

I have to extract data from file POD this time and get the POD dates respectively with their matched order number values already in Masterfile

Note: Every row will have a different order number, so not really any chances of over-driven data

Sub uploadPODdata()
 Dim WScopy As Worksheet, WSdest As Worksheet, desWB As Workbook, FileToOpen As Variant, cRow As Long, lastRow As Long, fnd As Range, PO As Range
 Set desWB = ThisWorkbook
 Set WSdest = desWB.Sheets(1)
 Application.ScreenUpdating = False
 FileToOpen = Application.GetOpenFilename(Title:="Browse for your file & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
 If FileToOpen = False Then Exit Sub
 Set OpenBook = Application.Workbooks.Open(FileToOpen)
 With Sheets(1)
 cRow = .Cells(Rows.Count, "C").End(xlUp).row
 For Each PO In Range("C5:C" & cRow)
 Set fnd = WSdest.Range("G:G").Find(PO, LookIn:=xlValues, lookat:=xlWhole)
 If Not fnd Is Nothing Then
 fnd.Offset(, 1) = PO.Offset(, 1)
 End If
 Next PO
 End With
 With WSdest
 cRow = .Cells(.Rows.Count, "A").End(xlUp).row
 .Range("N4:N" & cRow).Formula = "=if(E4=F4,M4*1,M4*0)"
 End With
 ActiveWorkbook.Close False
 Application.CutCopyMode = False
 Application.ScreenUpdating = True
End Sub

Perfect thanks! thats whole alot of help!

Mumps

Worked!!! but i am eventually putting an IF condition to calculate a function in a completely new column having no data before or after uploading that now showing an error. This function was working when i was filling POD data (ColumnE) manually.

Basically what i want to do is as soon as POD is uploaded, OTIC should be calculated automatically for all the rows having data in ColumnJ which has a condition that

ColumnI= If

D4=E4 then I4*1

else I4*0

Sub uploadPODdata()
 Dim WScopy As Worksheet, WSdest As Worksheet, desWB As Workbook, FileToOpen As Variant, cRow As Long, lastRow As Long, fnd As Range, PO As Range
 Set desWB = ThisWorkbook
 Set WSdest = desWB.Sheets(1)
 Application.ScreenUpdating = False
 FileToOpen = Application.GetOpenFilename(Title:="Browse for your file & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
 If FileToOpen = False Then Exit Sub
 Set OpenBook = Application.Workbooks.Open(FileToOpen)
 With Sheets(1)
 cRow = .Cells(Rows.Count, "J").End(xlUp).row
 For Each PO In Range("J3:J" & cRow)
 Set fnd = WSdest.Range("B:B").Find(PO, LookIn:=xlValues, lookat:=xlWhole)
 If Not fnd Is Nothing Then
 fnd.Offset(, 3) = PO.Offset(, 1)
 End If
 Next PO
 lastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
 .Range("J4:J" & lastRow).Formula = "=if, E4 = D4 then I4*100 else I4*0"
 End With
 ActiveWorkbook.Close False
 Application.CutCopyMode = False
 Application.ScreenUpdating = True
End Sub

Yes, i want another button (a new macro) , for example named as "Upload POD Data" the way same i was uploading previously which you suggested, this time i want it to match the data like for instance:

copying data from Purchase file, as an example PO number "3" so it should search in Masterfile and paste the POD date in COlumnE ("7th Jan") for this example, in the row where PO number "3" is located or Found i.e in E6

its like updating the masterfile with Purchase of Order Dates (POD) with their respective PurchaseORder (PO) numbers.

Thankyou for all the efforts!

Mumps

Tried everything but all in vain since hours!

- I just basically wants to update the data by matching its PO numbers

-Uploading the data from browser and pasting it Masterfile attached below by button name "Upload POD"

- As both the workbooks have a same column name "PO" so while pasting it should be pasted into the respective row where that "PO" in the master file is location

-As for example In Masterfile B5 has PO of "2" so when uploading the document having Proof data occurs the value mentioned in PO of "2" in file Purchase should be paste corresponding row5 in POD columnE , the value against PO number 2 is 3rd Jan

I have to extract data from file purchase this time and get the POD dates respectively with their matched PO values already in Masterfile

Note: Every row will have a different PO, so not really any chances of over-driven data

 Set desWB = ThisWorkbook
 Set WSdest = desWB.Sheets(1)
 Application.ScreenUpdating = False
 FileToOpen = Application.GetOpenFilename(Title:="Browse for your file & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
 If FileToOpen = False Then Exit Sub
 Set OpenBook = Application.Workbooks.Open(FileToOpen)
 With Sheets(1)
 cRow = .Cells(Rows.Count, "A").End(xlUp).row
 .Range("J4:K" & cRow).copy
 n = Application.Match((OpenBook.Range("J:J"),( WSdest.Range("C:C"), 0))
WSdest.Range("E" & n).Value = Cells.Range("E:E")
 WSdest.Cells(WSdest.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
 End With
 ActiveWorkbook.Close False
 Application.CutCopyMode = False
 Application.ScreenUpdating = True
End Sub

Here

Code:
Sub upload_data()
Dim WScopy As Worksheet, WSdest As Worksheet, desWB As Workbook, FileToOpen As Variant, cRow As Long
Set desWB = ThisWorkbook
Set WSdest = desWB.Sheets(1)
Application.ScreenUpdating = False
FileToOpen = Application.GetOpenFilename(Title:="Browse for your file & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
If FileToOpen = False Then Exit Sub
Set OpenBook = Application.Workbooks.Open(FileToOpen)
With Sheets(1)
cRow = .Cells(Rows.Count, "A").End(xlUp).row
.Range("A4:H" & cRow).copy
WSdest.Cells(WSdest.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
End With
ActiveWorkbook.Close False
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Quote from AlanSidman

Please add code tags to your post.

No help to be offered OP until this request is completed.

Kindly close this thread. Thankyou

Hi!

What i am trying to code is

-Uploading the data from browser and pasting it Masterfile attached below by button name "Upload POD"

- As both the workbooks have a same column name "PO" so while pasting it should be pasted into the respective row where that "PO" in the master file is location

As for example In Masterfile B5 has PO of "2" so when uploading the document having Proof data occurs the value mentioned in PO of "2" in file Purchase should be paste corresponding row5 in POD columnE , the value against PO number 2 is 3rd Jan

- I just basically wants to update the data by matching its PO numbers

Note: Every row will have a different PO, so not really any chances of over-driven data

for reference here is the code im using to upload data at first place mention in the macro of "UploadData"

@codeL:

Sub upload_data()
Dim WScopy As Worksheet, WSdest As Worksheet, desWB As Workbook, FileToOpen As Variant, cRow As Long
Set desWB = ThisWorkbook
Set WSdest = desWB.Sheets(1)
Application.ScreenUpdating = False
FileToOpen = Application.GetOpenFilename(Title:="Browse for your file & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
If FileToOpen = False Then Exit Sub
Set OpenBook = Application.Workbooks.Open(FileToOpen)
With Sheets(1)
cRow = .Cells(Rows.Count, "A").End(xlUp).row
.Range("A4:H" & cRow).copy
WSdest.Cells(WSdest.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
End With
ActiveWorkbook.Close False
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Masterfile.xlsmpurchase.xlsx

C

Quote from AlanSidman

Please add code tags to your post.

No help to be offered OP until this request is completed.

Apologies, can you tell me how to add code tags?

Quote from Mumps

Try:

Code

Sub upload_data()
 Dim WScopy As Worksheet, WSdest As Worksheet, desWB As Workbook, FileToOpen As Variant, cRow As Long, lastRow As Long
 Set desWB = ThisWorkbook
 Set WSdest = desWB.Sheets(1)
 Application.ScreenUpdating = False
 FileToOpen = Application.GetOpenFilename(Title:="Browse for your file & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
 If FileToOpen = False Then Exit Sub
 Set OpenBook = Application.Workbooks.Open(FileToOpen)
 With Sheets(1)
    cRow = .Cells(Rows.Count, "A").End(xlUp).Row
    .Range("A4:H" & cRow).copy
    With WSdest
        .Cells(WSdest.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
        lastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Range("I4:I" & lastRow).Formula = "= G4/(F4 - H4)"
    End With
 End With
 ActiveWorkbook.Close False
 Application.CutCopyMode = False
 Application.ScreenUpdating = True
End Sub

Display More

In your Master file insert "IFCR" in cell I3 before running the macro.

WORKED THANKYOU!!!

Apologies

here are both the workbooks

Workbook named as otc is where im extracting the data

Workbook named as Masterfile is the destination where the copied data is getting pasted

Thankyou for all the efforts!Masterfile.xlsmotc.xlsx