Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

PIvot Table or Macro or another way to get each job associated with a task

in excel, I'm trying to take a list of jobs and a list of codes and get a list where each job is listing in column A and each of the tasks in the other list are listed in column B for each job.
Jobs-and-Tasks.xlsx
0
janhearn
Asked:
janhearn
  • 12
  • 11
1 Solution
 
jppintoCommented:
Attached you will find a working sample using the code below. Just click on the button to build the list.

Hope this is what you want.

jppinto
Sub MakeList()
Dim lstRowA As Long
Dim lstRowB As Long
Dim x As Integer
Dim y As Integer
Dim rowtarget As Long
Dim ws As Worksheet
rowtarget = 1

Set ws = Sheets("Sheet1")

lstRowA = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
lstRowB = ActiveSheet.Cells(ActiveSheet.Rows.Count, "B").End(xlUp).Row

For x = 2 To lstRowA
    For y = 2 To lstRowB
        ws.Cells(rowtarget, 4).Value = ws.Cells(x, 1).Value
        ws.Cells(rowtarget, 5).Value = ws.Cells(y, 2).Value
        rowtarget = rowtarget + 1
    Next y
Next x
    
End Sub

Open in new window

Jobs-and-Tasks.xlsm
0
 
janhearnAuthor Commented:
What button do I click to build the list. Is this a macro. What are my steps?
0
 
jppintoCommented:
This is a macro. Click on the button that I've putted on the sheet that says "Make List". To add this macro to your sheet, just Alt+F11 takes you to the VB Editor. Select the sheet name on the right and add the code on the code window on the right.

jppinto
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
janhearnAuthor Commented:
I'm getting and error in VB
0
 
jppintoCommented:
Did you copy pasted this code into your own sheet?
0
 
jppintoCommented:
Can you post the error that you got? You got the error on my file or on yours?
0
 
janhearnAuthor Commented:
on your sheet. give me a minute and I'll tryo to follow your instructions to go to alt f11 and add my sheet.
0
 
jppintoCommented:
What is the error that you got?
0
 
janhearnAuthor Commented:
i had saved your file as a file on my system and see the Make List button - do I need to add the file if I do this?
0
 
jppintoCommented:
Not sure if I understood what you meant with "do I need to add the file if I do this", what file?!?
0
 
janhearnAuthor Commented:
When I try to add the macro to your file I hit alt f11 and get a black screen without a way to select a file. Is there a way you can log to my system and show me what to do?
0
 
janhearnAuthor Commented:
You sent back a working sample but when I hit the Make list button I get an error.
0
 
jppintoCommented:
You don't need to add the macro to my file! It's already in there! Take a look at the attached image. When you Alt+F11 on my file, and select "Sheet1" on the left, you will see my code on the code window on the right.
Capturar.JPG
0
 
janhearnAuthor Commented:
This is the error I get when I click the Make List button on your sample file.
untitled.bmp
0
 
janhearnAuthor Commented:
When I save your file to my system and hit alt f11 I don't get anything but a greyed out screen.
0
 
jppintoCommented:
I've made some small changes to my original code.

jppinto
Sub MakeList()
Dim lstRowA As Long
Dim lstRowB As Long
Dim x As Integer
Dim y As Integer
Dim rowtarget As Long
Dim ws As Worksheet
rowtarget = 1

Set ws = Sheets("Sheet1")

lstRowA = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
lstRowB = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

For x = 2 To lstRowA
    For y = 2 To lstRowB
        ws.Cells(rowtarget, 4).Value = ws.Cells(x, 1).Value
        ws.Cells(rowtarget, 5).Value = ws.Cells(y, 2).Value
        rowtarget = rowtarget + 1
    Next y
Next x
    
End Sub

Open in new window

Jobs-and-Tasks.xlsm
0
 
janhearnAuthor Commented:
I did save yours to my desktop and can see what suppose to when i hit alt f11. I need to add a code  - how can I adjust the tasks. I need to use this for another similar list - how do I edit?
0
 
janhearnAuthor Commented:
This is exactly what I needed. I'm going to try to copy this into my worksheet and have a list that includes another record. If that's the correct method to add something, we are complete. Please advise.
0
 
jppintoCommented:
Yes, just copy this code to other files that you want to use it. Just change the sheet name to other name if the sheet tab is no "Sheet1" and adjust the respective column if your file doesn't have the data in columns A and B. It should work on other files if you take care of this.

jppinto
0
 
janhearnAuthor Commented:
I added a code and it worked! This is great!
0
 
jppintoCommented:
Thanks.
0
 
janhearnAuthor Commented:
You were very patient with me. Thanks
0
 
jppintoCommented:
Ufff...I'm tyred! :) LOLOL This one took some finger work!

Thanks for your grade. Glad i could help.

jppinto
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 12
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now