From multi-column Database to a single-column one.

[Rudiakys]

Hello everyone.
I don't know how to make a database like this:

, look like this one:

.
Note that I don't simply have to transform it, but I have to leave (intact) the first one in its sheet, and have the result in another one.
Thank You.

 

[Eraserhead]

I don't understand the question. What is the database store in? Access? Microsoft SQL Server?

 

[Rudiakys]

I don't understand the question. What is the database store in? Access? Microsoft SQL Server?

Excel.

 

[sushi]

The pics are not showing. Please try posting them again so we can see what you want to do in Excel.

 

[Rudiakys]

The pics are not showing. Please try posting them again so we can see what you want to do in Excel.

Hello everyone.
I don't know how to make a database like this: (first image), look like this one: (second image).
Note that I don't simply have to transform it, but I have to leave (intact) the first one in its sheet, and have the result in another one.
Thank You.

 

[Eraserhead]

Can you add more data to either row?

 

[mkrishnan]

Do you mean that you want to do it dynamically (like with Excel formulas, macros, or actions), without modifying the original sheet? And is the original sheet completely disorganized like the example you gave (no headers or labels, extraneous data in some entries)?

Look up the built-in help for "pivot tables" -- this might do what you want, although the tables are assembled expecting the existence of column/row headers.

Now the snark would be to say that you should have whoever creates the originals for you learn how to use Excel, or at least communicate with other human beings. 😛

 

[Rudiakys]

Can you add more data to either row?

Yes. Also, since this is only a simlified example of what the final sheets would be, there could be more columns.
The fact is: I dont know hom many rows or columns there would be, because the first scheet is a result of another scheet either.

Do you mean that you want to do it dynamically (like with Excel formulas, macros, or actions), without modifying the original sheet? And is the original sheet completely disorganized like the example you gave (no headers or labels, extraneous data in some entries)?

Look up the built-in help for "pivot tables" -- this might do what you want, although the tables are assembled expecting the existence of column/row headers.

Now the snark would be to say that you should have whoever creates the originals for you learn how to use Excel, or at least communicate with other human beings. 😛

Yes, there would be labels on the actual tables I'm going to deal with.
Trying to pose my problem in other words: I'd like to know if there is some way to have a plain list of those nouns with their quantities attached. Maybe a formula that copies only things which are different from zero and empty cells.

 

[Eraserhead]

Now the snark would be to say that you should have whoever creates the originals for you learn how to use Excel, or at least communicate with other human beings. 😛

The snark in me would say that you should use a real database 😛. In this case that even includes Access 😱.

 

[mkrishnan]

The snark in me would say that you should use a real database 😛. In this case that even includes Access 😱.

Yeah, things like this are so much easier, even in Access....

I think that Pivot Tables should be able to be cajoled into what you want. The blank cells should be fine. I'm not 100% sure about how they'll handle all the zeros, though. I'm sure you could clean that up with some macros or Visual Basic, but that gets awfully silly...

What's the volume here... are we talking about doing this hundreds or thousands of times -- like on a daily basis?

 

[dejo]

And the snark in me says that even the simplified version is still multi-column.

 

[sushi]

As others have suggested, a database may be the way to go.

FWIW, using Excel as a database can be a real pain. Additionally, it's easy to cause your database to become disjoined (corrupted).

Rule of thumb:

- If you are going to be querying your data, then use a database.

- If you are going to be calculating based upon your data, then use a spreadsheet.

From the sounds of it, it looks more like data queries are the goal. Although to be honest, I still am not sure exactly what you want to do but understand that you don't want to post real data. The nice thing about databases is that they handle varying amounts of records (data) very conveniently. Excel Macros and such are much easier to do when the data elements are constant in number.

Database wise, as some have suggested, there is Access on the Windows side. FileMaker Pro which is cross platform might be worth a look. Or maybe an SQL solution.

 

[Rudiakys]

All right then. I'm going to have a look at some database software, thankyou.

 

[balamw]

- If you are going to be calculating based upon your data, then use a spreadsheet.

Unless of course you have mountains of data, in which case a database is still the right way to store the data, maybe with a spreadsheet front end. 😉

All right then. I'm going to have a look at some database software, thankyou.

Microsoft offers a reduced version of their SQL Server (a real database) software for free if you want to stay on Windows:

http://www.microsoft.com/Sqlserver/2005/en/us/express.aspx

Otherwise, you may want to look at MySQL which is cross platform.

B

 

[Eraserhead]

All right then. I'm going to have a look at some database software, thankyou.

As a serious lesson for future readers the only reason you would attempt something like what you suggested in the OP would be if you had a lot of data to start with that was already in such a system so it was going to be prohibitively expensive to change to using a database.

Even in the case of the OP it is definitely worth spending a bit of time making sure the right solution is used for the problem - as not doing that will cost a lot of time (and therefore money) to get the right solution at a later point - or you'll waste a lot of time and money trying to do things in a more difficult way than necessary.