PDA

View Full Version : create an id generator and copy data within worksheets



endout
11-18-2015, 07:19 AM
I have a workbook which should be used to display data about the current stock in an warehouse. The current data will be imported into the workbook and shoudl then be displayed in one worksheet. I've attached a sample workbook so that you can see what I'm trying to do.
First of all: The ID Generator.
In this example it is possible for one part to be stored in diferent warehouses. There could be 1 copper bolt produced by company1 in store1, while there are 7 in store2. In order to accumulate these positions i figured it would be best to give them individual ids. I think that implementing an individual id genrator would be better than using GUIDs. I've already created the basiscs for the generator, which you can find in the "id" worksheet. The first column of each table is for the (part)id and the second one for the description. Some companys only produce specific products so that the third table consist of a companyID and materialID and then the description.
I've already tried mapping the descriptions to the id by using a combination of VLookUp and Offset, but that doesnt seem to work.

Secondly, after generating the ID the accumulated data should then be displayd in the "data" worksheet. So in the first column should be the 8 copper bolts produced by company1 which are stored in store1 and 2. For that i figured I could use VLookUp to create references rather than copying the data but that would have to be dynamic so that only so many rows are filled as there actually are, rather than filling the whole sheet with the correct formula.

mancubus
11-18-2015, 07:57 AM
what is wrong with this thread yours, since you did not reply over my last post and opened a new thread instead?

http://www.vbaexpress.com/forum/showthread.php?54249-Importing-Worksheets-from-different-Workbook

endout
11-18-2015, 08:05 AM
I figured that (in the name of order and seo) that it would be better to discuss different tasks in different threads rather than using just one thread which started out with a different task. But if that's wrong I could continue the old thread.
I thought that i would have repiled to you in the other thread but appearently notebook swallowed that reply.

mancubus
11-18-2015, 08:52 AM
that's not wrong... a preference perhaps...

but providing a link to the previous may help members to see borader Picture of the Project and i find it useful.

and since you did not give feedback (or mark the thread as solved) we dont know if the latest code posted worked for you or not.



i believe a member will find a solution to your requirement.
cheers.

SamT
11-18-2015, 08:59 AM
For Warehousing activities, you will need several IDs


Internal Part Number
Location (Store # + Warehouse # + Aisle # + Rack # + Shelf # + Bin #)
Manufacturer's ID
Manufacturer's Part Number


Because some parts may be made by different companies, if a Universal ID system is available for that part (Bolt, 1/2x14x3" grade 5,) you should use that ID even thought it introduces inconsistencies in Part number styles, (34812048 = Widget, model 42)

IMO, you need one master list of all Part IDs , with descriptions and Min|Max Qty's, for the purpose of insuring that there are no duplicates.

The Location List must allow for duplicate Part numbers to handle multiple locations of the same part, (Don't tell me that never happens, I've worked warehouses before. :D) Include qty per locations here.

The Internal Part Number to Manufacturer's Part Number/Manufacturer cross reference list must also handle multiple entries.

The Manufacturer's ID must match the ID in the Manufacturer's database.

As you can see, once you have multiple entries of a part number, VLookUp will no longer work and you must resort to using VBA and VBA UserForms to accomplish what you need.

endout
11-18-2015, 10:05 AM
Yeah, I can see that it might be just a deal of preference.

SamT: Every dataset already has an unique, internal number. But I'm trying to merge existing data records in one spreadsheet. A typcial dataset consist of the manufacturer, material and product-type, and each set has an unique id (startet once at 1 and is now in the 100ks), but while a bolt may have that number i can't use it to merge all records of that particular mdoel into one sheet. So, basically my goal is to get an overview of the total amount of products of company1, which might then be broken down into the individual products of that manufacturer including the location, material, etc.
Thats why I made the ID Generator, although i might flip the current structure, so that the id is on the right side of the product and save myself some inverse vlookup-mess.
What I'm trying to do is not the typical warehouse setting, it was just the closest example i could find.

SamT
11-18-2015, 02:26 PM
We can only address the info you give us. Your descriptive language is inconsistent with Excel and Databases


https://en.wikipedia.org/wiki/Data_set:
Most commonly a dataset corresponds to the contents of a single database table (https://en.wikipedia.org/wiki/Table_%28database%29), or a single statistical data matrix (https://en.wikipedia.org/wiki/Data_matrix_%28multivariate_statistics%29), where every column (https://en.wikipedia.org/wiki/Column_%28database%29) of the table represents a particular variable, and each row (https://en.wikipedia.org/wiki/Row_%28database%29) corresponds to a given member of the data set in question.
A "DataSet" would not have a unique ID. I think you mean "Record"


https://en.wikipedia.org/wiki/Row_(database)
In the context of a relational database (https://en.wikipedia.org/wiki/Relational_database), a row—also called a record (https://en.wikipedia.org/wiki/Record_%28computer_science%29) or tuple (https://en.wikipedia.org/wiki/Tuple)—represents a single, implicitly structured data (https://en.wikipedia.org/wiki/Data) item in a table (https://en.wikipedia.org/wiki/Table_%28database%29). In simple terms, a database table can be thought of as consisting of rows and columns (https://en.wikipedia.org/wiki/Column_%28database%29) or fields (https://en.wikipedia.org/wiki/Field_%28computer_science%29).[1] (https://en.wikipedia.org/wiki/Row_%28database%29#cite_note-1) Each row in a table represents a set of related data, and every row in the table has the same structure.

What you said:
SamT: Every dataset already has an unique, internal number.

What I think you mean
Every record already has an unique, internal number.

If that is the case, then the IDs are not corresponding to part numbers, but merely the number of records in the database.

Since your goal is to find Unique Parts by manufacturer, then Unique Parts in total, regardless of manufacturer, I would start by Advanced Data Filtering the entire list for Unique Records only. Then I would sort the data by Manufacturer + Part number and save the workbook and only mess with copies of it in the future.

With a copy, I would then sort the table by Description + Manufacturer. After Mentally reviewing those results, I would choose the next step.

Be aware that it will probably take a lot of manual work to get your final product ready. I can see adding a Part ID column and autofilling it with a series of (part ID) numbers, but then having to keep shifting it down while adding a duplicate ID to a duplicate part by a different manufacturer. The hassle is because all manufacturers do not use identical Descriptions for identical parts.

Keep only the Part ID, Manufacturer, (ID,) and Manufacturer's Part number columns and you have the Local Part ID to Manufacturer's Part number Cross reference table.

With another copy, delete all but the Manufacturers columns and Filter it for Uniques, assign Ids and the Manufacturer to ID cross reference list is done. With VBA you can replace the Manufacturers on another list with their IDs.

VBA can also go thru a list and copy each manufacturer's section to an appropriately named Sheet for each different Manufacturer.

Just curious: How many unique parts are we talking about?

endout
11-19-2015, 03:43 AM
Yes, I'm talking about records not datasets. English is not my first language and afterwards I thought that records woud have been correct too.

We're talking about 50k records in different worksheets. These do however consist of around 400 individual products available in ~1000 different variations made by ~200 different companys.
Every record consisting of company, product , variation has an unique id number.
In the worksheet for my ID Generator I have a list of the different companys, products, etc and the id (or rather part of it) each dataset should get (company1 would get 001, etc.)

However I might not need this if it's possible to filter the datasets based on the Name (which will always be unique and identical for the same product). My goal is to filter the data in the source files to a mastersheet. I've already sorted the records alphabetically by manufacturer and product name. The filtered list should however not only consit of the indiviual products made by that company (for that i could just look at the sorted and maybe filtered source file) but also be merged, so that i may see that there are 15 products made by company 1 in the warehouse.

SamT
11-19-2015, 07:47 AM
These do however consist of around 400 individual products available in ~1000 different variations made by ~200 different companys..
Every record consisting of company, product , variation has an unique id number. Does that mean that there only ~ 1000 different Unique ID numbers?


My goal is to filter the data in the source files to a mastersheet.I don't think that a single Master Sheet is enough to organize that kind of data in a way that can be used.

I would limit the "Master Sheet" to an Internal or local Part ID number based on the Name (which will always be unique and identical for the same product.) Call it the Master Part Numbers Sheet.

I would give each supplier company their own Worksheet, but with a Local or Internal Part ID Column for cross reference.

Finally you would need a Local ID to Manufacturers Name cross reference list.

The Locations Sheet: Even though the Location Column is unique values, the Local Part ID may have duplicates. This list is the only one the Warehousemen need.

Purchasing will obviously need a Local Part ID to Company cross reference.

Master Part IDs Sheet = Unique Local IDs and Unique Names
Company Sheets = Unique Local IDs and Unique Manufacturer's Part numbers (the same local Part ID on many sheets)
Location Sheets = Unique Locations and some duplicate Local Part IDs.
Local To Manufacturer list = Duplicate Local Part IDs and Many Companies

endout
11-19-2015, 08:02 AM
Every record has an unique id, so thats about 50k IDs (generated by the database where the source data comes from).
However, I'm interested in an ID Genrator for the company, product and description.
product1, variation1 made by company1 would get the ID 001001001
product2, variation1 made vy company1 would get the ID 001002001
etc.
I've just filtered my list and got around 400 products/varaiations and thus the ID Generator would only need to create 400 IDs.

As I've said, I'm just not quite sure anymore whether i actually need that ID or if it would be easier to check the name and work with that. Just for the display or sorting it probably would not matter that much but if i want to count the instances of one product an ID might be easier (provided excel does not mess those up, i.e by incrementing them while copying).

The Purchase o products is not a problem, I'm just trying to create a spreadsheet which can then be used to get an overview of the items in stock (including graphs etc.) which the database-system can't handle.

SamT
11-19-2015, 03:15 PM
product1, variation1 made by company1 would get the ID 001001001
What about
product1, variation1 made by company2

same product, different ID?

It is my understanding that most Databases can handle what you need, It just takes a little creativity when writing Queries.

endout
11-20-2015, 01:08 AM
It would not be the same product, since every company produces different products. The ID for product1, variation1 by company2 would be 002001001. company1 may only produce bolts, while company2 may only produce nuts, etc. In general the company names are differet and so are the product names of these companys. The reason i can't get the data out of my database (in the form I need) is beacause I have to work with the data given (changing or creating a transaction for the database itself would take to long, since i dont have the permission to create those). Furthermore, my results should be given to inexperienced users who would just need to get an overeview over the current stock items without knowing what excel or the database did or did not.EDIT: I would need the IDs to create dependent comboboxes. If i were to select "company1" in the first combobox the second combobox should only offer products made by that company (by filtering or checking al the rows which have the companyID an displaying only these products)

SamT
11-20-2015, 08:34 AM
I see. Well, good luck.

endout
11-24-2015, 07:50 AM
Okay, I now have a way to create my IDs. I'll loop through the columns in my IDGenerator-Worksheet in orde to find the company name and assign the ID in the colum next to it to another variable. With that variable I can loop through the products etc.However, I'm currently also trying to create dependable/cascading comboboxes. I've created multiple activeX comboboxes (because they support typing and autofill). If I select "company1" in the first combobox, the second one (for prdocuts) should only display the products made by that particular company. I think the code itself would work, but I'm not using the orrect syntax or commands to populate the activeX ComboBox. Here's my code, could someone check that?



Sub ComboBox1_Change()

company = Worksheets("ids").ComboBox1.Value

lr = ThisWorkbook.Sheets("ids").Cells(Rows.Count, 1).End(xlUp).Row

Worksheets("data").comboProd.Clear



'loop through companynames

For x = 2 To lr

If company = ThisWorkbook.Sheets("ids").Cells(x, 1) Then

compid = ThisWorkbook.Sheets("ids").Cells(x, 2)

End If

Next x



'loop through products

For x = 2 To lr

If compid = ThisWorkbook.Sheets("ids").Cells(x, "D") Then

Worksheets("data").combProd.AddItem ThisWorkbook.Sheets("ids").Cells(x, 4)

End If

Next x

End Sub