Copying data from different column

[maryj]

I have data such as:

GLN Master SAP
1234 3333
4444
4567 7878
8888
9999
6789 4545
3535

This was created from a pivot. At each change in GLN (Col A) I need it to
select the first value in SAP (Col C) and paste in all rows for the group in
the Master column (Col B) The result like:
1234 3333 3333
3333 4444
4567 7878 7878
7878 8888
7878 9999
6789 4545 4545
4545 3535

the number of rows for each GLN group will vary - usually 2 but sometimes
more.

Thanks for you help!!

 

[meh2030]

I have data such as:

GLN        Master      SAP
1234                      3333
                             4444
4567                      7878
                             8888
                             9999
6789                      4545
                             3535

This was created from a pivot. At each change in GLN (Col A) I need it to
select the first value in SAP (Col C) and paste in all rows for the groupin
the Master column (Col B) The result like:
1234     3333          3333
            3333          4444
4567     7878          7878
            7878          8888
            7878          9999
6789     4545          4545
            4545          3535

the number of rows for each GLN group will vary - usually 2 but sometimes
more.

Thanks for you help!!

MaryJ,

See the code below.

Best,

Matthew Herbert

Sub FillMaster()
Dim lngCnt As Long
Dim rngGLN As Range
Dim rngSAP As Range
Dim rngCell As Range
Dim intOffset As Integer

'assuming "SAP" is in C1
lngCnt = Range(Range("C1"), Range("C1").End(xlDown)).Rows.Count

'offset column from GLN
intOffset = 2

'create range object to loop through
Set rngGLN = Range(Range("A2"), Range("A" & lngCnt))

'loop through each cell in GLN
For Each rngCell In rngGLN.Cells

'if the cell is not blank then get the corresponding SAP value
If rngCell <> "" Then
Set rngSAP = rngCell.Offset(0, intOffset)
End If

'output the SAP value into Master
rngCell.Offset(0, intOffset - 1).Value = rngSAP.Value
Next

End Sub

 

[maryj]

Perfect!!!

Thanks so much!
--
maryj

MaryJ,

See the code below.

Best,

Matthew Herbert

Sub FillMaster()
Dim lngCnt As Long
Dim rngGLN As Range
Dim rngSAP As Range
Dim rngCell As Range
Dim intOffset As Integer

'assuming "SAP" is in C1
lngCnt = Range(Range("C1"), Range("C1").End(xlDown)).Rows.Count

'offset column from GLN
intOffset = 2

'create range object to loop through
Set rngGLN = Range(Range("A2"), Range("A" & lngCnt))

'loop through each cell in GLN
For Each rngCell In rngGLN.Cells

'if the cell is not blank then get the corresponding SAP value
If rngCell <> "" Then
Set rngSAP = rngCell.Offset(0, intOffset)
End If

'output the SAP value into Master
rngCell.Offset(0, intOffset - 1).Value = rngSAP.Value
Next

End Sub