Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 76
Default Copying data from different column

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!!

--
maryj
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 135
Default Copying data from different column

On May 6, 8:56*am, maryj wrote:
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!!

--
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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 76
Default Copying data from different column

Perfect!!!

Thanks so much!
--
maryj


" wrote:

On May 6, 8:56 am, maryj wrote:
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!!

--
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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copying data from 205 columns to 1 column Zuo Excel Worksheet Functions 4 February 22nd 10 05:18 PM
copying data to a named column on a different worksheet jefheff Excel Discussion (Misc queries) 0 December 18th 08 12:54 AM
Need help copying data from one column to specific columns [email protected] Excel Programming 0 April 30th 07 04:56 PM
Copying Data into Column with Existing Data GZul Excel Discussion (Misc queries) 0 February 10th 06 12:30 AM
Copying a formula in a blank column as far as data in previous column basildon Excel Programming 1 December 16th 05 04:32 PM


All times are GMT +1. The time now is 10:04 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"