Mass data transfer

Hi

Using your 2 files, put the macro below into a general module in oxno.xls. Have both spreadsheets open and run. Check the output and see how it goes.

Sub aaa()
  Dim DataSH As Worksheet
  Set DataSH = Workbooks("ox.xls").Sheets("MDI-Shreya4")
  Workbooks("oxno.xls").Activate
  Sheets("sheet1").Activate
  lastdatarow = DataSH.Cells(Rows.Count, 1).End(xlUp).Row
  For Each ce In Range("A4:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    datarow = Evaluate("=SUMPRODUCT(--('[ox.xls]MDI-Shreya4'!$B$2:$B$" & lastdatarow & "=""" & ce.Value & """),--('[ox.xls]MDI-Shreya4'!$G$2:$G$" & lastdatarow & "=""" & ce.Offset(0, 1).Value & """),(ROW('[ox.xls]MDI-Shreya4'!$C$2:$C$" & lastdatarow & ")))")
    If datarow > 0 Then
      Cells(ce.Row, "N").Value = DataSH.Cells(datarow, "I").Value
      'Cells(ce.Row, "N").Value = datarow
      If removenum(Trim(DataSH.Cells(datarow, "L").Value)) = 1 Then
        Cells(ce.Row, "O").Value = DataSH.Cells(datarow, "L").Value
      ElseIf removenum(Trim(DataSH.Cells(datarow, "M").Value)) = 1 Then
        Cells(ce.Row, "O").Value = DataSH.Cells(datarow, "M").Value
      End If
      If removenum(Trim(DataSH.Cells(datarow, "J").Value)) = 1 Then
        Cells(ce.Row, "T").Value = DataSH.Cells(datarow, "J").Value
      ElseIf removenum(Trim(DataSH.Cells(datarow, "M").Value)) = 1 Then
        Cells(ce.Row, "T").Value = DataSH.Cells(datarow, "M").Value
      End If
    End If
  
  Next ce
  
  
End Sub

Function removenum(xx) As Integer
  Set regex = CreateObject("Vbscript.regexp")
  With regex
    .Pattern = "[0-9]"
    .Global = True
    removenum = Len(.Replace(xx, ""))
  End With
  Set regex = Nothing
End Function

rylo

Wow rylo thanks a lot for that, you are a real legend!

Field "N" is being generated perfectly. Field "O" is copied over correctly more than not, but no data at all is being copied over for "T"

Nowshad

Hi

Using the example file that we have been using, can you give instances of
1) Field O that is incorrect, what it should be and why.
2) Examples of Column T where it should be bringing back a result.

rylo

Duh, stupid me, please find attached the outcome.

I will focus on the first set of results (line 4-17 on spreadsheet "oxno" & lne 2-79 on spreadsheet "ox") and fill the fields in light yellow where nothing was copied over to field "O" and pale blue for field "T".
Also there was one instance where the field "N" was not copied over, I have put that in rose.

Thanks again for all your help it is really appreciated!

Hi

Looking quickly at some of the results in oxno.xls!O, you have entries that have 2 non numeric characters, yet your requirement was

its of numerical value with one non numerical field in it (usually a "/").

. Can you please clarify.

Also, I applied the same logic to the data for column T. Does this not apply?

For the same block in rows 4-17, can you please explain for each and every one WHY the item was selected, where it came from (row number) and the logic that selected that item. Update the example files and put the reasons etc into the next columns (P and U).

rylo

Sorry you are correct I did state originally I only needed data to be transfered that was numerical with max of 1 non numerical character, can I correct myself and request 5 non-numerical charcters?
Again I did not clearly state the requirments for Field "T" so appologies for that. This field will have similar data as field "N" so the requirements for field "T" should be the same as field "N".

Thanks

Hi

When you say 5, do you mean that it must have 5 non numeric, or up to and including 5 non numeric (excluding spaces).

rylo