Still looking for that miracle

rsw8679 · Mar 28th, 2002, 08:39 AM

A few days ago I wrote a question that looks at info on spread sheet 1 in 2 columns and 1 row, matches it to info that is in 3 columns on a second spread sheet and returns the result of the 4th column back to the first spread sheet. The question is located at:

http://mrexcel.com/board/viewtopic.p...3353&forum=2&1

Is what I am trying to do impossible? creating additional columns is not practical as it is the basis for doing the value of a sewer system. Columns are for pump station number and year, the row is the various diameters, no 2 pump stations are the same. I will also need this for gravity sewer where I will match 105 years in a column with 30 possible diameters in a row.

sann0638 · Mar 28th, 2002, 09:03 AM

Can you not just do this with a pivot table? If all the data is already in sheet2, and you want to sort it into the format given, create a pivot table with station and year in the side panel and diameter in the top panel. Remove subtotals by left-clicking on each field.

Apologies if I have misunderstood your request.

Dave Patton · Mar 28th, 2002, 09:18 AM

With a clear concise question and a very simple example, you would probably have received many excellent answers.

In the cell where you need the number, consider the following. Revise the ranges and criteria cells to fit your information.

=SUMPRODUCT((B1:B4=G3)*(C1:C4=G1)*(D1:D4=G2)*(E1:E4))

rsw8679 · Mar 28th, 2002, 09:42 AM

The simplest I can describe what's happening is this: Spread sheet 2 is an ASCII dump from a GIS data base and contains pump station, year, diameter, and length information for pipes. I need to retrieve the pipe lengths from spread sheet 2 based on a given pump station, year, and diameter and insert them into spread sheet one for calculations of depreciated value.

I am currently looking into the possibility of pivot tables but am a rookie at anything more than simple spread sheets.

Ian Mac · Mar 28th, 2002, 10:05 AM

I have studied your e.g. and I'd say that Daves answer is the one you need.
I am wondering why all the cells you mention re: sheet1 are so erractically placed, is this because there's a graphic or something on that sheet?

Mar 28th, 2002, 10:05 AM	#5
Ian Mac MrExcel MVP Join Date: Feb 2002 Location: Newcastle, UK Posts: 1,174	I have studied your e.g. and I'd say that Daves answer is the one you need. I am wondering why all the cells you mention re: sheet1 are so erractically placed, is this because there's a graphic or something on that sheet? __________________ "Have a good time......all the time" Ian Mac

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 28th, 2002, 08:39 AM	#1
rsw8679 New Member Join Date: Mar 2002 Posts: 5	A few days ago I wrote a question that looks at info on spread sheet 1 in 2 columns and 1 row, matches it to info that is in 3 columns on a second spread sheet and returns the result of the 4th column back to the first spread sheet. The question is located at: http://mrexcel.com/board/viewtopic.p...3353&forum=2&1 Is what I am trying to do impossible? creating additional columns is not practical as it is the basis for doing the value of a sewer system. Columns are for pump station number and year, the row is the various diameters, no 2 pump stations are the same. I will also need this for gravity sewer where I will match 105 years in a column with 30 possible diameters in a row.

Mar 28th, 2002, 09:03 AM	#2
sann0638 Board Regular Join Date: Mar 2002 Posts: 175	Can you not just do this with a pivot table? If all the data is already in sheet2, and you want to sort it into the format given, create a pivot table with station and year in the side panel and diameter in the top panel. Remove subtotals by left-clicking on each field. Apologies if I have misunderstood your request.

Mar 28th, 2002, 09:18 AM	#3
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	With a clear concise question and a very simple example, you would probably have received many excellent answers. In the cell where you need the number, consider the following. Revise the ranges and criteria cells to fit your information. =SUMPRODUCT((B1:B4=G3)(C1:C4=G1)(D1:D4=G2)*(E1:E4))

Mar 28th, 2002, 09:42 AM	#4
rsw8679 New Member Join Date: Mar 2002 Posts: 5	The simplest I can describe what's happening is this: Spread sheet 2 is an ASCII dump from a GIS data base and contains pump station, year, diameter, and length information for pipes. I need to retrieve the pipe lengths from spread sheet 2 based on a given pump station, year, and diameter and insert them into spread sheet one for calculations of depreciated value. I am currently looking into the possibility of pivot tables but am a rookie at anything more than simple spread sheets.