Excel database query question.?

Question

Hello, I have to use only excel or its plugins to do this as my boss wants me to automate this process for lazy people who are not advance computer useres.

I have been trying to get a custom report for which I have a table and a query that fileters the data for me.

I have six columns (Machine type, machine description, base warranty, 1 year service, 2 year service, 3 year service)

There are 3 records(rows) having the same same value for Machine type "4400", machine description "Big machine", base warranty "1".

Now the 1st row has a serial number under 1 year service and no values for the rest of the remaining columns.
2nd row has a serial number under 2 year service and no values for the rest of the remaining columns.
3rd row has a serial number under the 3 year service and no values for the remaing columns.

How to automate this that I get one single row so that I avoide dublicates in first 3 columns and the years serial number come on seperate columns givin me 1row

Answer 1

First of all, get this utility called ASAP utilities from www.asap-utilities.com.

And just to be sure, this is how I understand your Excel to be:

Column1.....Column2.....Column3
1 yr serv......2 yr serv.....3 yr serv
xyz
..................abc
....................................lmn

Is this right? (ignore the dots, I put those for formatting only)

Ok, once you've installed it, follow this procedure:

1. Select the three rows where the data needs to be merged into a single row. eg. u would need to select the rows that contains xyz, abc and lmn values

2. Go to this menu option in Excel:
ASAP Utilities > Columns/ Rows > Merge Row data (join cells)

3. In the next dialog box that pops-up, just click OK since the values that are being merged are in different columns and you don't need to add any delimiters.

ASAP takes some getting used to so make sure you try all this on a copy of your spreadsheet.

Answer 2

Excel isn't a database, it is a spreadsheet, and yes, Excel makes nice pretty rows and columns, but if you want to manipulate data like that, import the spreadsheet into Access (which is a true database) and you will be able to make all kinds of reports, what shows up on your reports, be able to make forms for users who aren't very computer "savvy" and restrict what they can enter and change, or Access will not let them save the info if they haven't filled in all the required data. Access is a fantastic database program where Excel is a great tool for calculations. hope that helps.

Answer 3

Sounds like the database needs to be scrubbed. No point in writing code to fix bad data. Clean it up in the db and make a single row (unless that's improper for some reason). Then you won't have to worry about writing code. Good database design solves a host of otherwise thorny issues.

Answer 4

you'd be desiring some type of CGI programming to do this. which means dynamic pages written in some thing like Hypertext Preprocessor, Perl, C, Java, etc. i'd recommend Hypertext Preprocessor because that is probable the finest, and lowest discovering curve. what's CGI? It really means "dynamic content textile". you're probable acquainted with trouble-free HTML pages. they're only archives that you've, and they get further as-is for your customers. each and each and every time the webserver receives a request for an HTML web page, it only sends the HTML report-- extremely common. yet CGI is different. somewhat than a flat report, that is a software whose output receives despatched to the top person. So on each and every occasion the webserver receives a request for a CGI software, the webserver runs this technique, and sends the output of that software to the person. the shopper could nevertheless see HTML, although that is created on-the-fly through your CGI software. So this technique can do all varieties of such issues as write output to a database, examine some thoughts, ensure the userid, etc, etc. yet having CGI on your web site will recommend you pick an ISP who will enable that. examine together with your ISP to make certain in the experience that they enable scripting or no longer, and what varieties of dynamic internet content they enable. in the experience that they don't enable it, you pick a sparkling ISP. yet in the previous you rush on your thanks to a sparkling ISP, yet another ingredient to envision for is a few type of database skill as well. you pick to keep those contact lists someway, or maybe as you may want to invent your own report format to keep them, i'd caution you hostile to it. usually because there is countless holes for novices to fall into that couldn't bite you till your customers commence utilising the equipment. besides, i'd recommend MySQL, yet there's one of those different stuff accessible. next up, get to grips with the CGI language and database of your determining on. Make some common CGI scripts that use trouble-free varieties and form processing. Then make certain the thanks to make certain your databases. Make some attempt tables, attempt information-- make certain the thanks to artwork with it. finally, hook the CGI into the database through writing to it even as your form processing CGI's are called. Now you're prepared to jot down your software for actual. DaveE

Answer 5

It can be done using pivottable tables in Excel. But its hard to give you an example as I have diffiiculty figuring what your basic problem is. Email me a sample set, on another sheet show how you want the result to look like.