convert range to database


Hello, everyone: 

I would like to convert the range from Sheet 2 into database style in sheet 1. is there any way possible to accomplish this using only formulas in sheet 2? please note, the data in sheet 1 goes on continuous. I have added 2 "Test ID"s as an example.

6 Replies



As of this writing, you've had 85 views of your question and no replies. I'm just jumping in to offer the observation that it's not at all clear what you're seeking to do here.


You say you want to convert the range from Sheet 2 into database style in sheet 1, but then go on to ask if it's possible to do so using only formulas in sheet 2.


But Sheet 2 is the source of the data. My guess is that the question about formulas in Sheet 2 must be a typo, that you really mean for the formulas to do their work in Sheet 1. Correct?


But then there's the matter that many of your headings (most?) are inconsistent between the two Sheets. I have no doubt that you know your way around here, know that Sheet 2's "Expected Strength" with a parenthetical reference to psi underneath it, refers to "Required" on Sheet 1....  ETC.


Then there's the matter of all kinds of fancy formatting having been applied before the desired functionality has been achieved. You've heard of the notion of putting the cart before the horse, I presume. It really is a mistake to make things look fancy and pretty before they work.


All of that to say....can we back up and ask what you're really needing to do from a functional or utility point of view? Why does data in a perfectly good database (i.e., your Sheet 2) need to get rendered in an altogether different way? Are you expecting it all to get populated, with every data element from your Sheet 2 arrayed differently (somewhat confusedly) on your Sheet 1? Does it all have to be there? Could we create a "dashboard" that only extracts one ID at a time, or one set of related IDs?


It very definitely is possible to extract data from a database, and even to make it look nice (after you've figured out the extraction process itself), but let's begin first with a bigger picture (more full description) of what this is all about.

sorry, i did have a small typo in my question. I am using Sheet1 to manually maintain my data on a day to day basis, and i want to, somehow, convert it to sheet 2 to import it to computer software.
Did you read the rest of what I wrote? There were quite a few more questions asked. I think if you're looking for help, you'd have a better chance of getting that help if you gave your would-be helpers some help in the bigger picture.....

ok, here is an example of what i did earlier, but i needed to update my "input sheet" aka sheet2.

I'm sorry, but I'm still waiting for my initial questions to be answered.
There may not be a good solution for this request. The objective is to convert Sheet 1 (Vertically arranged data good for analysis) to look like Sheet 2 (Merged cells, different header names, fewer records, etc.). Typically, the request would be the opposite, make Sheet 2 looks like Sheet 1. I understand different systems require data to be arranged in unorthodox ways to make sheet import-ready.

If I was to take on this project, I'd use a pivot table to re-shape the data in Sheet 1 and fix the rest manually. I don't think Power Query or vba are of any use here.