It can be done in SQL, but how about with an XL formula?

[brianqaf]

EXAMPLE.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3			SALESSTATS:0.01-0.5011-10-2007						SALESANALYSIS(HEADOFFICE)
4				GOOD	MEDIUM	POOR			STAT	COLOUR	QUALITY	VALUE
5			RED	8.5	7.4	6.1			0.05	RED	GOOD	8.5
6			ORANGE	7.4	6.6	5.5			51.89	RED	POOR
7			GREEN	6.7	6	5			100.02	GREEN	GOOD
8									150	ORANGE	MEDIUM
9			SALESSTATS:0.51-1.0011-10-2007
10				GOOD	MEDIUM	POOR
11			RED	11.3	9.8	8.2
12			ORANGE	10.2	8.4	7.3
13			GREEN	9	7.2	6.5
14
15		SHEET1						SHEET2
16
Sheet1

The above represents two worksheets I am using, in one excel workbook.

On sheet 2, in the value column, I want to (somehow) grab the corresponding value from sheet 1.
eg. (the first line on sheet 2) I want to find the value, where stat is 0.05, colour is RED & quality is GOOD.
On sheet 1, this number is found in the cell D5 (highlighted pink)
In a simple world, in my answer cell, I would manually type '=sheet1!D5' and do the same for all the values I'm looking for, but the document above is simplified. The actual sales stats sheet is massive (71,380 rows of figures), hence I don’t want to spend the next millenia manually clicking.
Is there something I can do to automate this? I've considered a macro, but I don’t think that’s 'do-able' because (to me) it seems more like something one would use SQL for, not a 'copy what I do' macro.

I'm not an SQL programmer by any means but I reckon (in simple language) it would read like this; (by the way, I'm not asking for an SQL answer, I know excel must be able to do this with formulas)

[TYPED in cell L5 (where I want the answer displayed)]
FIND table in SHEET 1, where table title (C3) contains the value in cell I5 (ie; 0.05 is within 0.01 - 0.50)
FIND ROW in that table where colour (the first cell along) matches the value in cell J5
FIND COLUMN in that table where quality (the top cell in that table) matches the value in cell K5
WHERE the ROW and COLUMN above cross each other (CELL D5), take that value and put is in cell L5

Hope I've made it simple. Hope you can help.

Thanks Everyone.

 

[Norie]

Brian

Is that exactly how Sheet1 is structured?

If it is then I don't see how this would be possible using SQL.

It could be possible using formulas but the structure isn't very conducive towards that.

As to code, if that is the exact structure then that could be the way to go.

 

[brianqaf]

Hey Norie,

Thanks for your (really quick) response!

I appreciate what you asked about the sheet format:

The excel document is how it is on the actual sheet (but the two bold boxed areas are different worksheets in the workbook) - labbelled sheet 1 and sheet 2.

Maybe if we/I break the question down.

If I were to just be working with ONE TABLE ONLY (array C3:F7)
ie, I'll change the formula myself for each individual table.

Could the formula be possible then? Making a row be selected by row title(eg cell J5 selecting C5:F5) and a column selected by column title (eg cell K5 selecting D4:D7), then where to two selected lines cross over, could the cell contents be displayed in the answer cell?

*Just want to say thanks, even with these questions its making me think deeper and from different angles about this question - who knows I might even get the answer myself!

Thanks superstars.

 

[njimack]

Norie's right, your layout doesn't lend itself to using formulae. That said, IF you were using a single table, and based on your existing layout, the following should work:

=INDEX(Sheet1!B6:D8,MATCH(J5,Sheet1!A6:A8,0),MATCH(K5,Sheet1!B5:D5,0))

You could *probably* tweak this to work with multiple tables if you named each datarange, and then used INDIRECT to figure out which table to retrieve the data from.

It's a very messy way of doing things, though. You'd be better off trying to combine your Sales Stats tables, if possible.

 

[brianqaf]

Hey NJIMack,

Thankyou so much for sending that formulae, it certainly explains a lot of the formulation I was messing with (whilst not knowing what on earth I was doing) lol. you guys are great!

I think with what I've got there I should be able to copy the formula and edit it for each table.

Despite being a mamouth (ish) task, its certainly A LOT easier than what I was dreading I'd end up doing...

Thanks again.

*I'll post more if I get stuck or I come up with some other question.

 

[Norie]

brian

Is there any way you could change the format of the first sheet?

Perhaps just temporarily in a new sheet to get the required results?

If this is a viable option then I'm sure we could come up with some code to get the data into a more acceptable format.

That shouldn't be too hard especially if the layout of the tables is uniform throughout as in your example.