Pulling Data from an Excel Spreadsheet to Generate Worksheets?

[ctackett6407]

Greetings,

I'm not sure exactly where to start, but I have an idea of what I'm trying to do. I tried to think of a way to import all these excel files into an Access Database.. but I am not very skilled, I can use the out of the box stuff for Access, but can't seem to get it to do what I'm trying to get done.

I process 100's of orders a day. The way our process is right now, it is VERY redundant. (something that seems to only need to take 20 mins.. can take 7 hours, easy. Since we are manually entering all the data it leaves room for a LOT of human error)

My goal is to consolidate this into as few steps as possible.

I cannot bypass manually entering the order into our Great Plains software, but after I enter all the orders in the Great Plains software I can go in and export an excel spreadsheet that contains information that I can use in my 2nd purchase order I have to complete.

I have to make an excel spreadsheet for every purchase order I complete. I have to enter every Item Number, Style, PO#, Unit Cost, etc... and this is the part that takes hours.

Is there a way that Excel 2016 can pull all that data from another excel file that I export from Great Plains and generate worksheets and pull the data to fill into designated fields on the excel purchase order?

For example;

I enter 30 purchase orders into Microsoft Great Plains, The orders can range from 10 different items to 100 different items of various quantities...

When I'm done I generate a report on all the PO's that were created by me that day..

I get a file that looks something like this


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]PO Number
[/TD]
[TD]PO Line Status
[/TD]
[TD]PO Type
[/TD]
[TD]Item Number
[/TD]
[TD]Item Description
[/TD]
[TD]Vender ID
[/TD]
[TD]Location Code
[/TD]
[TD]QTY
[/TD]
[TD]Unit Cost
[/TD]
[TD]Extended Cost
[/TD]
[TD]Document Status
[/TD]
[TD]PO Status
[/TD]
[/TR]
[TR]
[TD]PO1111
[/TD]
[TD]Released
[/TD]
[TD]Normal
[/TD]
[TD]11111
[/TD]
[TD]Item Desc
[/TD]
[TD]1010
[/TD]
[TD]Main
[/TD]
[TD]1999
[/TD]
[TD]$0.92
[/TD]
[TD]AnotherNumberHere
[/TD]
[TD]Not Important
[/TD]
[TD]Not Important
[/TD]
[/TR]
[TR]
[TD]PO1111
[/TD]
[TD]Released
[/TD]
[TD]Normal
[/TD]
[TD]11112
[/TD]
[TD]Something2
[/TD]
[TD]1010
[/TD]
[TD]Main
[/TD]
[TD]1233
[/TD]
[TD]$1.33
[/TD]
[TD]AnotherNumberHere
[/TD]
[TD]Not Important
[/TD]
[TD]Not Important
[/TD]
[/TR]
[TR]
[TD]PO1111
[/TD]
[TD]Released
[/TD]
[TD]Normal[/TD]
[TD]11113
[/TD]
[TD]Something3
[/TD]
[TD]1010
[/TD]
[TD]Main[/TD]
[TD]1236
[/TD]
[TD]$0.26
[/TD]
[TD]AnotherNumberHere
[/TD]
[TD]Not Important
[/TD]
[TD]Not Important[/TD]
[/TR]
[TR]
[TD]PO1111
[/TD]
[TD]Released
[/TD]
[TD]Normal[/TD]
[TD]11114
[/TD]
[TD]Something4
[/TD]
[TD]1010
[/TD]
[TD]Main
[/TD]
[TD]134243
[/TD]
[TD]$0.88
[/TD]
[TD]AnotherNumberHere
[/TD]
[TD]Not Important
[/TD]
[TD]Not Important[/TD]
[/TR]
[TR]
[TD]PO1112
[/TD]
[TD]Released
[/TD]
[TD]Normal
[/TD]
[TD]11115
[/TD]
[TD]Something5
[/TD]
[TD]1010
[/TD]
[TD]Main
[/TD]
[TD]1513
[/TD]
[TD]$1.99
[/TD]
[TD]AnotherNumberHere[/TD]
[TD]Not Important
[/TD]
[TD]Not Important[/TD]
[/TR]
[TR]
[TD]PO1113
[/TD]
[TD]Released
[/TD]
[TD]Normal[/TD]
[TD]11116
[/TD]
[TD]Something6
[/TD]
[TD]1010
[/TD]
[TD]Main
[/TD]
[TD]153122
[/TD]
[TD]$2.88
[/TD]
[TD]AnotherNumberHere
[/TD]
[TD]Not Important[/TD]
[TD]Not Important[/TD]
[/TR]
[TR]
[TD]PO1113
[/TD]
[TD]Released
[/TD]
[TD]Normal
[/TD]
[TD]11117
[/TD]
[TD]Something7
[/TD]
[TD]1010
[/TD]
[TD]Main
[/TD]
[TD]6364
[/TD]
[TD]$2.99
[/TD]
[TD]AnotherNumberHere[/TD]
[TD]Not Important
[/TD]
[TD]Not Important
[/TD]
[/TR]
[TR]
[TD]PO1114
[/TD]
[TD]Released
[/TD]
[TD]Normal
[/TD]
[TD]11119
[/TD]
[TD]Something8
[/TD]
[TD]1010
[/TD]
[TD]Main
[/TD]
[TD]464353
[/TD]
[TD]$2.00
[/TD]
[TD]AnotherNumberHere[/TD]
[TD]Not Important[/TD]
[TD]Not Important[/TD]
[/TR]
[TR]
[TD]PO1114
[/TD]
[TD]Released
[/TD]
[TD]Normal[/TD]
[TD]11118
[/TD]
[TD]Something9
[/TD]
[TD]1010
[/TD]
[TD]Main
[/TD]
[TD]3646
[/TD]
[TD]$1.99
[/TD]
[TD]AnotherNumberHere[/TD]
[TD]Not Important
[/TD]
[TD]Not Important
[/TD]
[/TR]
</tbody>[/TABLE]

I have an excel spreadsheet that I then have to transfer, manually, the information in bold into..

I'm still going to have to manually enter a few things in as the excel generated from Microsoft Great Plains doesn't show Style.. but.. If there was a way a worksheet could be generated from the excel for each of the "different" PO Numbers in the excel file (example above).. and then insert the data into fields into that PO.. automatically..

Like all the Line items from PO# 1111 ... would go to Worksheet PO# 1111 .. and then all the line items associated with that PO in the above Table would auto populate in the excel file.. and then all I had to do was go in and add the style .. or a couple other things.. but all the line items would already be done.

Any feedback would be great! I've been working on this for a week.. only had this job for a month, but I'm not keeping up to speed because it's like I'm doing the work over and over.. when it could be simplified.

 

[vcoolio]

Hello CTackett6407,

As a starting point, here's a possibility which may work for you.

Let's assume that the first Excel worksheet into which the data is imported is now the Main or Master sheet. Using the following code, new sheets are created from the PO numbers in Column A and all the relevant rows of data are transferred to the relevant individual sheet:-

Sub CreateNewShtsTransferData()

    Dim sht As Worksheet
    Dim lr As Long, x As Long
    Dim ID As Object
    Dim key As Variant

Set sht = Sheet1
Set ID = CreateObject("Scripting.Dictionary")
     
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
     
lr = sht.Range("A" & Rows.Count).End(xlUp).Row
     
For x = 2 To lr
        If Not ID.Exists(sht.Range("A" & x).Value) Then
            ID.Add sht.Range("A" & x).Value, 1
       End If
Next x

For Each key In ID.keys
        If Not Evaluate("ISREF('" & key & "'!A1)") Then
        Worksheets.Add(After:=Sheets(Sheets.Count)).Name = key
        End If
    
sht.Range("A1:A" & lr).AutoFilter 1, key
    sht.[A1].CurrentRegion.Copy Sheets(key).[A1]
        Sheets(key).Columns.AutoFit
            sht.[A1].AutoFilter
Next key

sht.Select
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "All done!", vbExclamation

End Sub

Following is the link to a small sample that I did for you just so that you have an idea of how it all could work. In the sample, click on the "RUN" button to see it work.

http://ge.tt/1mrVr3p2

I hope that this helps.

Cheerio,
vcoolio.

 

[ctackett6407]

Thank you,

I will try this now.

I hate asking questions, but I've been trying to figure this out for 3 weeks. I think my biggest problem is .. I need to sit down and knock out one small task of it at a time.. and it'll all come into place, but then I find people saying that I'm doing it wrong.. so I finally just asked.

Thanks again! I'll try this.

Hello CTackett6407,

As a starting point, here's a possibility which may work for you.

Let's assume that the first Excel worksheet into which the data is imported is now the Main or Master sheet. Using the following code, new sheets are created from the PO numbers in Column A and all the relevant rows of data are transferred to the relevant individual sheet:-

Sub CreateNewShtsTransferData()

    Dim sht As Worksheet
    Dim lr As Long, x As Long
    Dim ID As Object
    Dim key As Variant

Set sht = Sheet1
Set ID = CreateObject("Scripting.Dictionary")
     
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
     
lr = sht.Range("A" & Rows.Count).End(xlUp).Row
     
For x = 2 To lr
        If Not ID.Exists(sht.Range("A" & x).Value) Then
            ID.Add sht.Range("A" & x).Value, 1
       End If
Next x

For Each key In ID.keys
        If Not Evaluate("ISREF('" & key & "'!A1)") Then
        Worksheets.Add(After:=Sheets(Sheets.Count)).Name = key
        End If
    
sht.Range("A1:A" & lr).AutoFilter 1, key
    sht.[A1].CurrentRegion.Copy Sheets(key).[A1]
        Sheets(key).Columns.AutoFit
            sht.[A1].AutoFilter
Next key

sht.Select
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "All done!", vbExclamation

End Sub

Following is the link to a small sample that I did for you just so that you have an idea of how it all could work. In the sample, click on the "RUN" button to see it work.

http://ge.tt/1mrVr3p2

I hope that this helps.

Cheerio,
vcoolio.

 

[ctackett6407]

Thank you again vcoolio,

I'm trying to piece together my process of how I can do my job with this great new macro.

So after I process all my Purchase Orders in Great Plains .. I can export my excel file and then just rename the tab to "master" and run this..

This will then pull the data and split it up by Purchase Orders.

Now is this able to be customized?

See the next step is trying to get it to auto populate the Purchase Order I have to upload individually to suppliers. Below is an image of that


Basically the fields that I use, as of right now, are the PO Number, Item Number, Item Description, QTY Ordered, Unit Cost, and Extended Cost.

On the form I have to enter, the PO# has to go up at the top, the Item Number is called the "Art UPC" .. the Item Description is the "Item" .. and the other three are self explanatory.

I'm going to add a VLOOKUP eventually that will automatically populate the Hangtag and Hologram based on what's in the Item list.. And I'm going to try to figure out how to use Concatenate or something to copy the last few words of the "Item" to appear in the Pattern.

Then I'll only have to input the Style #.. Color.. Size.. and any specific customer information.. this will allow me to lessen human error.

Anyway, any direction would be great. I'm understanding concepts, just trying to find examples of how to do things is hard when I don't know exactly what I'm looking for.. I know what I'm trying to get it to do, but not sure how to explain it.

 

[vcoolio]

Hello CTackett6407,

Now is this able to be customized?

Yes, it can be customised. However, now that I'm beginning to understand the way that you would like this to work, could I suggest:-

1) Sort and process all your data in the Great Plains programme (as you will do anyway).
2) Export it to Excel as your Master data set (and, just to be original, name it "Master").
3) Rather than split the data into individual sheets named after the PO numbers, create a Template sheet for the Purchase Order form as per your image. This can then be your standard form for all of eternity.
4) When a Purchase Order needs to be filled in, a macro can be written to populate the PO form with details of each PO number. So, if, for example, PO#1111 has five entries in the Master sheet then all five rows of data can be populated on the PO form with the relevant data from the Master sheet. The company info can be manually entered or be populated via a drop down box ( to help reduce the possibility of User error).
5) Archive (to another sheet) the details just entered on the PO form then delete these details from the Master sheet.

Item 4 above may or may not suit as you may only want three of the five PO# details to populate the PO form. You may have to clarify this.

These are just ideas to help you on your way however it would be a great idea to manually create a mock-up of a workbook showing how you would like it to work for your particular situation. This is more homework for you but, if you do this, we will have a better understanding and can use the mock-up to test with. This may also entice other Volunteers to give some more input/ideas.

If you do create a mock-up, upload it to a free file sharing site such as ge.tt or Drop Box and then post the link to your file back here.

Cheerio,
vcoolio.

 

[ctackett6407]

Greetings and Thank you again

http://ge.tt/2VV784p2

I have uploaded my Purchase Order workbook to that location.

I'm still not good with macros, but I do have a macro that I found that will take all the tabs of a workbook and split up the workbook to reflect whatever name that tab is.. so if I name each tab the PO# .. it will save that file as it's own workbook with that PO#. This helped me when I had 150 PO's in 1 workbook.

I then wrote a batch file that generated 150 folders for PO# etc.. and then I just had to drag and drop all the workbooks into the appropriate folder.. and upload them to our suppliers site.

Anyway, here is what I'm working on right now.. (the above link).

I added the Master File for the first tab, I highlighted the items that my goal is to auto populate. I also thought of a couple things that I can try to do .. to make it easier.

If the "Vendor ID" is a certain number on the Master sheet.. then my goal is to make a VLOOKUP that will auto populate their address into the sheet. That doesn't sound too bad. I've figured out VLOOKup, which wasn't too bad. I also am going to try to get VLOOKUP to work with Hangtag and Hologram. If the Item field contains names like NFL team names.. then "if" the Style doesn't contain M* followed by a number.. then the Hangtag will be FBF-NFL; however, if the Style contains M*.. example M1234 and an NFL team is contained in the Item line.. then it will be AKM-NFL in the Hangtag field. I just have to learn conditional wording.. or something like that. I'm thinking it's something to do with IF or Else..

And another goal is to copy or duplicate the text in the item field.. after the Sports Team name... and put it in the patter..

Example: if the template pulls in Virginia Tech University or Univ Shout A/O - Primary .. then it would capture the "Shout A/O - Primary" and copy it to the Pattern field.. while leaving it in the Item field.

Anyway, this is where I'm starting. slowly but surely, but I'm learning as I do it!
[TABLE="width: 264"]
<colgroup><col width="264"></colgroup><tbody>[TR]
[TD="class: xl65, width: 264"][/TD]
[/TR]
</tbody>[/TABLE]

Hello CTackett6407,



Yes, it can be customised. However, now that I'm beginning to understand the way that you would like this to work, could I suggest:-

1) Sort and process all your data in the Great Plains programme (as you will do anyway).
2) Export it to Excel as your Master data set (and, just to be original, name it "Master").
3) Rather than split the data into individual sheets named after the PO numbers, create a Template sheet for the Purchase Order form as per your image. This can then be your standard form for all of eternity.
4) When a Purchase Order needs to be filled in, a macro can be written to populate the PO form with details of each PO number. So, if, for example, PO#1111 has five entries in the Master sheet then all five rows of data can be populated on the PO form with the relevant data from the Master sheet. The company info can be manually entered or be populated via a drop down box ( to help reduce the possibility of User error).
5) Archive (to another sheet) the details just entered on the PO form then delete these details from the Master sheet.

Item 4 above may or may not suit as you may only want three of the five PO# details to populate the PO form. You may have to clarify this.

These are just ideas to help you on your way however it would be a great idea to manually create a mock-up of a workbook showing how you would like it to work for your particular situation. This is more homework for you but, if you do this, we will have a better understanding and can use the mock-up to test with. This may also entice other Volunteers to give some more input/ideas.

If you do create a mock-up, upload it to a free file sharing site such as ge.tt or Drop Box and then post the link to your file back here.

Cheerio,
vcoolio.

 

[vcoolio]

Hello CTackett6407,

Well, its good to see that you don't want to be spoon fed! You're doing an excellent job at researching, testing and implementing on your own. You're on the right track with the VLookUps (or you could also use a more robust method: the index/match function).

I've attached a link to the sample you provided with the following code implemented:-

Sub TransferData()

        Dim wsM As Worksheet: Set wsM = Sheets("Master")
        Dim wsT As Worksheet: Set wsT = Sheets("Template")
        Dim PO As String
        Dim lr As Long
        Dim cArr As Variant, pArr As Variant

PO = wsT.[J3].Value
lr = wsM.Range("A" & Rows.Count).End(xlUp).Row
nrow = wsT.Cells(Rows.Count, 2).End(xlUp).Row + 1

cArr = Array("D2:D" & lr, "E2:E" & lr, "H2:H" & lr, "I2:I" & lr, "J2:J" & lr)
pArr = Array("H", "B", "J", "K", "L")

Application.ScreenUpdating = False

wsM.[A1].CurrentRegion.AutoFilter 1, PO
For x = LBound(cArr) To UBound(cArr)
        wsM.Range(cArr(x)).Copy
        wsT.Range(pArr(x) & nrow).PasteSpecial xlValues
        Next
wsM.[A1].AutoFilter

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

The code will filter Column A (PO Number) of the Master sheet for the PO placed in cell J3 of the Template sheet. It will then transfer the relevant five cell values (as per your post #4 ) for each row of the selected PO number to the Template. Your formulae should do the rest of the populating as you have described in your last post.

I would suggest that you unmerge J3 (merged cells create havoc with macros) and create a drop down box to hold all your PO numbers. You can then select the appropriate PO number from the drop down and click on "GO" (as per the sample) to populate the template with the relevant details of the selected PO number.

In the sample, for the sake of the exercise, I have just placed one PO number in J3 of the Template sheet.

http://ge.tt/1if485p2

I hope that this further advances your quest.

Cheerio,
vcoolio.

 

[ctackett6407]

Greetings again!

Thanks for the reply,

for some reason it says "Share File Not found."

 

[ctackett6407]

I'll try this again when I get home.

I wasn't able to download from the link, but I tried the code and it had a never ending list.. in the right spot, but a never ending list of information. It also didn't populate in j3.

I removed the merge, but will get to try it out more when I get home.

Hello CTackett6407,

Well, its good to see that you don't want to be spoon fed! You're doing an excellent job at researching, testing and implementing on your own. You're on the right track with the VLookUps (or you could also use a more robust method: the index/match function).

I've attached a link to the sample you provided with the following code implemented:-

Sub TransferData()

        Dim wsM As Worksheet: Set wsM = Sheets("Master")
        Dim wsT As Worksheet: Set wsT = Sheets("Template")
        Dim PO As String
        Dim lr As Long
        Dim cArr As Variant, pArr As Variant

PO = wsT.[J3].Value
lr = wsM.Range("A" & Rows.Count).End(xlUp).Row
nrow = wsT.Cells(Rows.Count, 2).End(xlUp).Row + 1

cArr = Array("D2:D" & lr, "E2:E" & lr, "H2:H" & lr, "I2:I" & lr, "J2:J" & lr)
pArr = Array("H", "B", "J", "K", "L")

Application.ScreenUpdating = False

wsM.[A1].CurrentRegion.AutoFilter 1, PO
For x = LBound(cArr) To UBound(cArr)
        wsM.Range(cArr(x)).Copy
        wsT.Range(pArr(x) & nrow).PasteSpecial xlValues
        Next
wsM.[A1].AutoFilter

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

The code will filter Column A (PO Number) of the Master sheet for the PO placed in cell J3 of the Template sheet. It will then transfer the relevant five cell values (as per your post #4 ) for each row of the selected PO number to the Template. Your formulae should do the rest of the populating as you have described in your last post.

I would suggest that you unmerge J3 (merged cells create havoc with macros) and create a drop down box to hold all your PO numbers. You can then select the appropriate PO number from the drop down and click on "GO" (as per the sample) to populate the template with the relevant details of the selected PO number.

In the sample, for the sake of the exercise, I have just placed one PO number in J3 of the Template sheet.

http://ge.tt/1if485p2

I hope that this further advances your quest.

Cheerio,
vcoolio.

 

[ctackett6407]

Right now I'm working on getting INDEX and MATCH to work with three columns which will speed that up.

I do have a question, I have searched the forums and also google. Is there a way to create a drop down list (like suggested above) and exclude duplicates.. without having to use a pivot table?