Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code help
Hi,
I was wondering if anyone can help with the following. I have 3 worksheets. Sheet 1 contains data like the following: Name Group --------- --------- David 1 Peter 1 Jen 2 Paul 3 Sheet 2 contains data like the following Group Date 1 Date 2 Date 3 ..... --------- --------- ---------- --------- 1 Yes Yes No 2 No Yes Yes 3 No No Yes Sheet 3 contains the following headings: Name Group Date 1 Date 2 Date 3 ...... -------- -------- --------- --------- ---------- I would like to write a bit of code that would populate Sheet 3 with the persons name and all the dates they have a "Yes" in, according to the group they are in. i.e. The code looks up Sheet 2 for the dates corresponding to that persons group and then populates sheet 3 with all the dates. Is this possible? Many thanks, David |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code help
Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim iLastRow As Long Dim iRow As Long With Worksheets("Sheet1") Worksheets("Sheet3").Range("A1:E1").Value = Array("Name", "Group", "Date 1", "Date 2", "Date 3") iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row For i = 2 To iLastRow Worksheets("Sheet3").Cells(i, "A").Value = .Cells(i, "A").Value Worksheets("Sheet3").Cells(i, "B").Value = .Cells(i, "B").Value iRow = Application.Match(.Cells(i, "B").Value, Worksheets("Sheet2").Columns(1), 0) Worksheets("Sheet3").Cells(i, "C").Value = _ Worksheets("Sheet2").Cells(iRow, "B").Value Worksheets("Sheet3").Cells(i, "D").Value = _ Worksheets("Sheet2").Cells(iRow, "C").Value Worksheets("Sheet3").Cells(i, "E").Value = _ Worksheets("Sheet2").Cells(iRow, "D").Value Next i End With End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Daveo" wrote in message ups.com... Hi, I was wondering if anyone can help with the following. I have 3 worksheets. Sheet 1 contains data like the following: Name Group --------- --------- David 1 Peter 1 Jen 2 Paul 3 Sheet 2 contains data like the following Group Date 1 Date 2 Date 3 ..... --------- --------- ---------- --------- 1 Yes Yes No 2 No Yes Yes 3 No No Yes Sheet 3 contains the following headings: Name Group Date 1 Date 2 Date 3 ...... -------- -------- --------- --------- ---------- I would like to write a bit of code that would populate Sheet 3 with the persons name and all the dates they have a "Yes" in, according to the group they are in. i.e. The code looks up Sheet 2 for the dates corresponding to that persons group and then populates sheet 3 with all the dates. Is this possible? Many thanks, David |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code help
On Sep 11, 11:02 am, "Bob Phillips" wrote:
Public Sub ProcessData() Const TEST_COLUMN As String = "A" '<=== change to suit Dim i As Long Dim iLastRow As Long Dim iRow As Long With Worksheets("Sheet1") Worksheets("Sheet3").Range("A1:E1").Value = Array("Name", "Group", "Date 1", "Date 2", "Date 3") iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row For i = 2 To iLastRow Worksheets("Sheet3").Cells(i, "A").Value = .Cells(i, "A").Value Worksheets("Sheet3").Cells(i, "B").Value = .Cells(i, "B").Value iRow = Application.Match(.Cells(i, "B").Value, Worksheets("Sheet2").Columns(1), 0) Worksheets("Sheet3").Cells(i, "C").Value = _ Worksheets("Sheet2").Cells(iRow, "B").Value Worksheets("Sheet3").Cells(i, "D").Value = _ Worksheets("Sheet2").Cells(iRow, "C").Value Worksheets("Sheet3").Cells(i, "E").Value = _ Worksheets("Sheet2").Cells(iRow, "D").Value Next i End With End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Daveo" wrote in message ups.com... Hi, I was wondering if anyone can help with the following. I have 3 worksheets. Sheet 1 contains data like the following: Name Group --------- --------- David 1 Peter 1 Jen 2 Paul 3 Sheet 2 contains data like the following Group Date 1 Date 2 Date 3 ..... --------- --------- ---------- --------- 1 Yes Yes No 2 No Yes Yes 3 No No Yes Sheet 3 contains the following headings: Name Group Date 1 Date 2 Date 3 ...... -------- -------- --------- --------- ---------- I would like to write a bit of code that would populate Sheet 3 with the persons name and all the dates they have a "Yes" in, according to the group they are in. i.e. The code looks up Sheet 2 for the dates corresponding to that persons group and then populates sheet 3 with all the dates. Is this possible? Many thanks, David Hi Bob, That's great - thankyou very much!! Cheers - David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming |