Formula Help

[rehberger]

I have spreadsheet with the data set up like the first table. It runs across for 100's of columns, these are just the first 3 categories. Is there a way to pull the amounts based on the location and acct# in a set up like the second table? All of the Acct#'s in the data are not the same in each row as shown in the 4th row. Thanks for any assistance.

Location	Acct#	Amount		Location	Acct#	Amount		Location	Acct#	Amount
Reno	1874	291.25		Land	1874	300.00		Atrium	1874	225.50
Reno	1875	100.00		Land	1875	200.00		Atrium	1875	25.00
Reno	1876	50.50		Land	1890	80.00		Atrium	1880	75.50

<tbody>
</tbody>

	Acct#	Amount
Reno	1874
	1875
	1876
Atrium	1874
	1875
	1890

<tbody>
</tbody>

 

[sandy666]

something like that?

Location	Acct#	Amount
Reno	1874​	291.25​
Reno	1875​	100​
Reno	1876​	50.5​
Land	1874​	300​
Land	1875​	200​
Land	1890​	80​
Atrium	1874​	225.5​
Atrium	1875​	25​
Atrium	1880​	75.5​

 

[Marcelo Branco]

Maybe something like this

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

1​

Location​

Acct#​

Amount​

Location​

Acct#​

Amount​

Location​

Acct#​

Amount​

2​

Reno​

1874​

291,25​

Land​

1874​

300,00​

Atrium​

1874​

225,50​

3​

Reno​

1875​

100,00​

Land​

1875​

200,00​

Atrium​

1875​

25,00​

4​

Reno​

1876​

50,50​

Land​

1890​

80,00​

Atrium​

1880​

75,50​

5​

6​

7​

8​

9​

Location​

Column​

Acct#​

Amount​

10​

Reno​

1​

1874​

291,25​

11​

1​

1875​

100,00​

12​

1​

1876​

50,50​

13​

Atrium​

9​

1874​

225,50​

14​

9​

1875​

25,00​

15​

9​

1880​

75,50​

16​

Land​

5​

1874​

300,00​

17​

5​

1875​

200,00​

18​

5​

1890​

80,00​

19​

Formula in B10 copied down
=IF(C10="","",MATCH(LOOKUP(2,1/(A$10:A10<>""),A$10:A10),$2:$2,0))

Formula in D10 copied down
=IF(C10="","",INDEX(INDEX(A:ZZ,0,B10+2),MATCH(C10,INDEX(A:ZZ,0,B10+1),0)))

Hope this helps

M.

 

[jtakw]

Hi,

If you don't mind copying the "Location" names in each subset down column A like my sample below.

Whether you keep or not keep the Blank row between subsets is up to you, if the blank row(s) between subsets are eliminated, we won't need to test B Column for Blanks.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Location	Acct#	Amount		Location	Acct#	Amount		Location	Acct#	Amount
2	Reno	1874	291.25		Land	1874	300		Atrium	1874	225.5
3	Reno	1875	100		Land	1875	200		Atrium	1875	25
4	Reno	1876	50.5		Land	1890	80		Atrium	1880	75.5
5
6
7		Acct#	Amount
8	Reno	1874	291.25
9	Reno	1875	100
10	Reno	1876	50.5
11
12	Atrium	1874	225.5
13	Atrium	1875	25
14	Atrium	1890	0
15
16
17		Acct#	Amount
18	Reno	1874	291.25
19	Reno	1875	100
20	Reno	1876	50.5
21	Atrium	1874	225.5
22	Atrium	1875	25
23	Atrium	1890	0
Sheet153

Cell Formulas
Range	Formula
C8	=IF(B8="","",SUMIFS(C$2:K$4,A$2:I$4,A8,B$2:J$4,B8))
C18	=SUMIFS(C$2:K$4,A$2:I$4,A18,B$2:J$4,B18)

Either formula copied down.