Multiple Components to a Product - How to Pull Efficently

[Rex32]

Hey Guys,

First off, great forum! I've found so much incredible information here!

Second - sorry for the awful thread name, I just can't think of how to describe what I'm trying to do.

Okay, down to business.

I have a report I can run from my manufacturing software that gives me a Production Number in column A, then the type of component (B), and lastly the exact component name (C). What ends up happening is one finished item will have 15 rows of data, all with the same production number. I need to pull out a certain set of data, in this case the type of coffee beans in the products. So I've sorted the data down so I have every production number and then each bean, but this still results in multiple production numbers.

Prod01 001-001 Mexico .85
Prod01 001-005 Brazil .15
Prod01 001-008 Hundo .07
Prod02 001-005 Brazil .80
Prod03 001-005 Brazil .45
Prod03 001-009 Africa .25

So that is how the data looks. What I want to create is a list of all the production numbers vertically and then a list of all the bean options horizontally. After doing this I need to fill in the weights in the respective columns per bean number (001-001, 001-005, etc).

I can post a sample of the data if that is more helpful, but I'm struggling with the best way to do this. There are about 1500 items so doing this even partially by hand is a huge undertaking. I was thinking of separating out each bean type and then using a vlookup per column, but that also seems time consuming.


Thank You Much!


-Rex

 

[Chris Mack]

Hi

This is working for me:

Sub CreateTable()
    a = "Sheet1"
    b = "Sheet2"
    n = Sheets(a).Cells(Rows.Count, 1).End(xlUp).Row
    For r = 1 To n
        If Evaluate("COUNTIF(Sheet2!A:A,""" & Sheets(a).Cells(r, 1) & """)") = 0 Then
            k = Sheets(b).Cells(Rows.Count, 1).End(xlUp).Row + 1
            Sheets(b).Cells(k, 1) = Sheets(a).Cells(r, 1)
        End If
        If Evaluate("COUNTIF(Sheet2!1:1,""" & Sheets(a).Cells(r, 3) & """)") = 0 Then
            k = Sheets(b).Cells(1, Columns.Count).End(xlToLeft).Column + 1
            Sheets(b).Cells(1, k) = Sheets(a).Cells(r, 3)
        End If
        insrow = Evaluate("MATCH(""" & Sheets(a).Cells(r, 1) & """,Sheet2!A:A,0)")
        inscol = Evaluate("MATCH(""" & Sheets(a).Cells(r, 3) & """,Sheet2!1:1,0)")
        If Sheets(b).Cells(insrow, inscol) <> "" And Sheets(b).Cells(insrow, inscol) <> Sheets(a).Cells(r, 2) Then
            Sheets(b).Cells(insrow, inscol) = "Multiple pieces of data were matched to this cell"
        ElseIf Sheets(b).Cells(insrow, inscol) = "" Then
            Sheets(b).Cells(insrow, inscol) = Sheets(a).Cells(r, 2)
        End If
    Next
End Sub

I'm assuming a setup of 'Sheet1' containing the data (starting in A1) and a blank sheet called 'Sheet2' ready to be populated with the new table.

Hope this helps,

Chris.

 

[Rex32]

Chris,

That is incredibly helpful!!!

I have just realized how lacking my Excel knowledge is! What would be a good source to start learning about how to build Macros like this? I can't believe how that just did that nearly instantly!


Thanks,

Rex

 

[Chris Mack]

You're welcome!

As for learning, what I feel worked best for me was to:-

1. Have something to work on, e.g., a project or something. Or think of little repetitive tasks that you feel could save a lot of time if they were automated, for example, deleting all zero values in a range or something like that. I guess building up slowly could be said to be important, but I also think being ambitious.

2. Perhaps the best tool is to record macros in Excel yourself, and then study the resulting code; this is a really quick way to pick up the general syntax of how to "talk to" Excel. Although the code you get won't always be the most efficient way of writing it, so you can also learn how to condense the resulting/recorded code, which will further help with learning syntax.

3. I've also found that asking and answering questions on this forum has been very helpful.

4. For me, at the end of the day, it's all about knowing what I want a macro to do, logically, and then just finding out how to write that into Excel/VBA, step by step.

Hope this helps,

Chris.

 

[Rex32]

Chris,

Thanks for that info! I work in Excel all day and started this job about a month ago. I'm responsible for running reports and generate new ones. This could be for a historical perspective, to project needs for our goods that will become finished goods, to help our sales teams, etc... The company has grown very quickly and the reporting has fallen by the wayside so it is a huge undertaking, but I am also afforded a lot of freedom and can build these reports however I see fit.

My current problem that I'm looking to simplify is this:

I have sales data in dollars, pounds, and inventory value (or cost). This data is stored monthly and goes back to 2008. I currently have all of this information in individual Excel woorksheets. So for Sales Dollars, there is a sheet with the list of ALL available item codes (about 10,000) with sales numbers for each month - so it ends up being a lot. When building reports I use VLOOOKUPs to grab the specific data I need, but ideally I'd like to create a database with the historical data and then a form type worksheet where I can input new data and it applies to to my database sheets.

This is the kind of stuff I'm tasked with figuring out and I really want to figure out very efficient ways and I usually have an idea of what I want to do and how - but I don't know where to start.

The project today was to associate the beans to each item so I have a master blend sheet. It would have taken me at least 10 hours to do manually. With your VBA code, I did it in under half an hour and that was after modifying, testing, etc. The data was there in under a minute. I'm getting to the limits of VLOOKUPs because I have so much data and there are so many different ways I need to be able to filter it, sort it, account for it, etc....

The other main project is to compile a master item list. This will have the item code as the reference and then everything one could need all across the sheet - this could be the size of the packaging, the blend information, etc...

Sorry getting kind of rambly, just nice to be able to post this to people that truly know Excel inside and out!


Thanks Again,

Rex

 

[Rex32]

In regards to books, it looks like 'John Walkenbach' has a good series of books on the matter. I'm going to pickup the Basic Programming and VBA Program one tomorrow, hopefully I can learn to build something like what Chris did for me and expand upon it!



Thanks,

Rex