Difficulty linking data together

[Cannycrow]

Hello, first time poster here but I have found the site very helpful to solve issues before but this time I cant find an answer.
I have an amount of data, I make windows and each window is given a type for simplicity. There maybe for instance 6No type 1 windows and each window has 2 panes of glass in it and tape 2 window has just 1 pane. Each type 1 window also has a unique reference W01, W02, W03 etc. I have been asked to provide a spread sheet that will link the glass for the window type to the window unique reference rather than just the type and this is proving rather difficult. My data comes from a programme and provides a window type and glass qty.
I can split down the Types with the glass widths/heights and can split down the Type with Unique reference both using Index and match (might not be the right way but has worked) but the way the data is listed I cant combine the two.
Really hope someone can help here as its driving me to despair seems so simple yet the answer eludes me.

 

[Micron]

I don't see how what you want is achievable because there is nothing unique about the reference values. If they were unique you would not have 2 or more records/rows with WO1 values. You need a column of values that are truly unique that describe reference and sizes together. I say both because you have the same combination of sizes for different references. Your sheet data should look like

UniqueRef	Type	Ref	Qty	Width	Height
1	Type1	WO1	1	1000	1500
2	Type1	WO1	1	800	450
3	Type1	WO3	1	1000	1500
4	Type1	WO3	1	800	450
5	Type1	WO9	1	1000	1500
6	Type1	WO9	1	800	450

IMO, you are trying to emulate what a relational database can do but you don't have the relationships you'd need. Not sure you can do this in Excel anyway because my expertise lies in databases rather than spreadsheets. Should you decide that Access might solve your problem, do not jump into table design without understanding db normalization, otherwise you will continually frustrate yourself.

 

[Cannycrow]

I don't see how what you want is achievable because there is nothing unique about the reference values. If they were unique you would not have 2 or more records/rows with WO1 values. You need a column of values that are truly unique that describe reference and sizes together. I say both because you have the same combination of sizes for different references. Your sheet data should look like

UniqueRef	Type	Ref	Qty	Width	Height
1	Type1	WO1	1	1000	1500
2	Type1	WO1	1	800	450
3	Type1	WO3	1	1000	1500
4	Type1	WO3	1	800	450
5	Type1	WO9	1	1000	1500
6	Type1	WO9	1	800	450

IMO, you are trying to emulate what a relational database can do but you don't have the relationships you'd need. Not sure you can do this in Excel anyway because my expertise lies in databases rather than spreadsheets. Should you decide that Access might solve your problem, do not jump into table design without understanding db normalization, otherwise you will continually frustrate yourself.

Thanks for the reply, Noted about your expertise I can't use a database. So if the W01 values had for instance a 1 added so read 1W01, 2W01 etc could it be made to work?

 

[Micron]

I believe so - if I interpret "linking" to be about relating to unique values. Look at your data with that in mind and imagine whether or not you can derive a single row/record by doing that. In other words, there should be only one combination of glass and sizes that would fit the unique values. As I mentioned, there is nothing unique about your reference values so uniqueness is what you need to achieve. Also consider the impact on where you use WO1 now as it will have a new leading character which could break formulas or references, or you might not like seeing it in things like reports. If you start over at each new WO group I think your idea will also tell you something about the number of WO#'s that you have, which might prove useful. That is, 1WO1, 2WO1, 1WO3, 2WO3, 3WO3...

Having said that, the normal approach with db's is that the primary key field be unique and not be real data, so an autonumber is used and it is never changed for any particular record. This has the benefit that when the boss wants WO's changed to something else it doesn't matter. The related field is the primary key and in the example I gave you that is the leftmost column.

 

[Cannycrow]

I believe so - if I interpret "linking" to be about relating to unique values. Look at your data with that in mind and imagine whether or not you can derive a single row/record by doing that. In other words, there should be only one combination of glass and sizes that would fit the unique values. As I mentioned, there is nothing unique about your reference values so uniqueness is what you need to achieve. Also consider the impact on where you use WO1 now as it will have a new leading character which could break formulas or references, or you might not like seeing it in things like reports. If you start over at each new WO group I think your idea will also tell you something about the number of WO#'s that you have, which might prove useful. That is, 1WO1, 2WO1, 1WO3, 2WO3, 3WO3...

Having said that, the normal approach with db's is that the primary key field be unique and not be real data, so an autonumber is used and it is never changed for any particular record. This has the benefit that when the boss wants WO's changed to something else it doesn't matter. The related field is the primary key and in the example I gave you that is the leftmost column.

Thanks again, ill try some ideas