Pulling data from one sheet to another based on various criteria's

[LittleJon]

I hope you can see the image on the link/attached. http://tinypic.com/r/atn28i/9

On the image it shows on the left hand side of the black separator is a blank manifest, the information on this sheet (which will be on worksheet 1) will always be the same. These are different countries I send mail to. For each country or region we have the 3 formats, which are Letters, Flats & packets. The no of items for letters to Denmark will always be in B30 of sheet 1.

On sheet 2, which is the right side of the black separator, the data is pulled from an external system and pasted in to sheet, of which the data will always start at A6.

What I'm trying to do is find the best way to pull the data from sheet 2, over to sheet 1 and to go into the relevant cells.

Things to note;

• Per country/Region there are 3 formats, letters, Flats & packets.
• The data on sheet 2 changes from day to day.
• If we have a country match from the data on sheet 2 with sheet 1, then the data would go into the appropriate country listing. If not then it would go into the region, If we have the data going into the country cells, then it can NOT also go into the region cells.

Example
On work sheet 2, it states we have 19 letters for Australia, so this would go into B39 on worksheet 1. It also states on worksheet 2, we have one letter for Guam. as we have no country listing for this on worksheet 1, then this would go into the Oceania region which would be B49.

I appreciate that the region name in sheet 2 is Australasia and it is Oceania on sheet 1.

Does anyone have any suggestions on the quickest way of doing this other than using a VLOOKUP in sheet 1 which would pull the data from sheet 2 after I have manipulated it into formats for the country listing as well as the region listings?

Any suggestions welcome.

Thanks

 

[DanteAmor]

It can be with a macro.
Before, create a sheet called "Sheet3"
Structure of the sheet3:


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Region[/TD]
[TD]Destination[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Autralasia[/TD]
[TD]Oceania[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Caribbean[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Then, execute the following macro:

Sub Pulling_Data()
    '
    Application.ScreenUpdating = False
    Set h1 = Sheets("Sheet1")   '
    Set h2 = Sheets("Sheet2")   ' the data from an external system
    Set h3 = Sheets("Sheet3")   ' Relation Region-Destination
    '
    u1 = h1.Range("A" & Rows.Count).End(xlUp).Row
    h1.Range("B30:G" & u1).ClearContents
    '
    u2 = h2.Range("A" & Rows.Count).End(xlUp).Row
    For i = 6 To u2
        w_cou = h2.Cells(i, "A").Value  'country
        w_for = h2.Cells(i, "C").Value  'format
        w_qty = h2.Cells(i, "D").Value  'qty
        w_wei = h2.Cells(i, "E").Value  'weight
        w_reg = h2.Cells(i, "F").Value  'region
        Select Case LCase(w_for)
            Case LCase("Letter"): col = 2
            Case LCase("Flat"):   col = 4
            Case LCase("Packet"): col = 6
        End Select
        Set b = h1.Columns("A").Find(w_cou, lookat:=xlWhole, LookIn:=xlValues)
        If Not b Is Nothing Then
            h1.Cells(b.Row, col).Value = w_qty
            h1.Cells(b.Row, col + 1).Value = w_wei
        Else
            Set b = h3.Columns("A").Find(w_reg, lookat:=xlWhole, LookIn:=xlValues)
            If Not b Is Nothing Then
                w_des = h3.Cells(b.Row, "B").Value  'destination
                Set b = h1.Columns("A").Find(w_des, lookat:=xlWhole, LookIn:=xlValues)
                If Not b Is Nothing Then
                    h1.Cells(b.Row, col).Value = w_qty
                    h1.Cells(b.Row, col + 1).Value = w_wei
                End If
            End If
        End If
    Next
    Application.ScreenUpdating = True
    MsgBox "End"
End Sub

Let me know any doubt.

 

[LittleJon]

DanteAmore, firstly thank you for your response and the time put in.

Sheet3 was created, Should B2 be a country and not a region? Im not sure why we would have regions in both A & B in sheet 3.

Whilst there has been a change in sheet 1 for the region names, I have changed the data in sheet 2 to match. The macro isn't pulling the data for the countries and putting them into the regional section, though it is pulling in the data for the countries.

Again, thank you for your help.​

 

[DanteAmor]

Sheet 3 is to relate the region to the destination, for example, Guam exists on sheet 2, but does not exist on sheet 1, Guam belongs to the region "Autralasia", but on sheet 1 you have it as "Oceania" , then the Guam data should be put in "Oceania". For that I need the sheet 3, so that you put the different names that have the regions "Autralasia" - "Oceania"