asked on

Storing 3 Dimensional Data in Excel

Hi all,

I have a "database theory" type question for spreadsheets.

Let's say we're dealing with a commodity that has something like 3 variables. For example, a car:

1. Make
2. Model
3. Color

So for example, a Chevy Malibu could come in different colors like: white, red, black, grey, etc.

What's the most efficient way to store the data in a spreadsheet so that queries and data extractions are efficient?

Thanks in advance.

Bill Prew

In general I would say just a three column sheet with those columns. However, it really depends on how you will use that data, and that could change the best organization if we knew more about that.

»bp

Go-Bruins

ASKER

Hi,

Do you mean something like this?

https://goo.gl/W1sgxX

It seems that it goes against the theories of normalization, etc.

SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Go-Bruins

ASKER

Thanks Martin,

That kind of code is probably above my pay grade. What would the table actually look like?

SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bill Prew

And agreed, this isn't "normalized", but in your care you need to know configurations, so I expect some repetition. I doubt it makes sense to have three different base tables, one each for Make, Model and Color. Because they are of limited value independently. You can't associate ANY model with ANY make, etc.

»bp

Martin Liss

That kind of code is probably above my pay grade. What would the table actually look like?

I wasn't actually thinking about a table.

Please tell me/us more about how the data would be used.

Go-Bruins

ASKER

Thanks.

Bill - referring to your picture, is there any way to "tidy" up the data so that I don't have to see "Chevy" and "Blazer" on 3 different rows?

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Go-Bruins

ASKER

Thank you, one and all. I think the way you guys laid out the data is probably the best way, and as you mentioned, the data extraction would involve pivot tables, etc.

Martin Liss

You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2017
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2017