match fetch query between 2 spreadsheets, help appreciated

[monometh]

Hi,

I've not dealt much with the match technique and although have tried it myself, I don't seem to have it right.. hope people can help out.

I've got 2 spreadsheet, a data sheet let's say and the main sheet.

within the data.xls i have => Let's say 123 below starts at row 2

col a (head id)
123
123
123
456
456
789
789

col b (sum t1)
100
200
200
300
300
600
600

col c (Subtotal) -> which is a subtotal of col b according to ID of col a
0
0
500 (made up of 100, 200 and 200 sumed according to ID 123)
0
600 (made up of 300 and 300 sumed according to ID 456)
0
1200 (made up of 600 and 600 sumed according to ID 789)

the way the formulae is in col C, it will only show the subtotal of that ID on the last row of the occuring ID, so there are three occurence of 123 ID, but the subtotal of 500 will occur on the last row of 123, in this case, if row 123 starts at row 2, than 500 above should occur at C4.

anyway,

I have the main.xls as follows:

col b (Group ID) (same as head id in data.xls sheet)
col c (Total) (this is the same as Subtotal col c of data.xls)

basically, i require a match that looks at col b (group id) of main.xls and looks for match in data.xls at column a of head id, if it finds a match, looks at column c subtotal of data.xls and looks for the subtotal (occuring on the last row of the series of the same ID which gives 500),

This 500 should now be pasted into col c (total) of the main.xls

could someone please help with this one.. i'm stumped..

 

[Krishnakumar]

Re: match fetch query between 2 spreadsheets, help appreciat

Hi,

Have a look..

Book3
	A	B	C	D
1	HeadID	Sum
2	123	100
3	123	200
4	123	200
5	456	300
6	456	300
7	789	600
8	789	600
Data

Now this is the Main Sheet..

Book3
	A	B	C	D
1	GroupID	Total
2	123	500
3	456	600
4	789	1200
5	150	0
Main

Is this what you are looking for?

Kris

 

[Aladin Akyurek]

Re: match fetch query between 2 spreadsheets, help appreciat

Is the data in data.xls sorted in ascending order on IDs as your sample implies? If so, you could avoid (re-)calculating the subtotals in main.xls by doing a fast lookup.

 

[monometh]

Re: match fetch query between 2 spreadsheets, help appreciat

Hi Kris and Aladin,

Kris, you're not really got it right, the task ins't about subtotaling it as the subtotal forumule is already there.. in column C of the data.xls

The task is more about matching the group id of the main.xls to the head id of the data.xls and than retrieving what is the in the data.xls to the main in column say in col C

Aladin, I could always sort the ID by ascending order, both the Id as in Head ID of data.xls and Group ID of main.xls

but is it possible to do without this?

Really need a hand with this one...

 

[Aladin Akyurek]

Re: match fetch query between 2 spreadsheets, help appreciat

...I could always sort the ID by ascending order, both the Id as in Head ID of data.xls and Group ID of main.xls

but is it possible to do without this?

...

Sorting the data on ID in data.xls would suffice...

Data.xls

Data.xls
	A	B	C	D
1	ID	T1	Subtotal
2	123	100
3	123	200
4	123	200	500
5	456	300
6	456	300	600
7	789	600
8	789	600	1200
Sheet1

C2, copied down:

=IF(A2<>A3,SUMIF($A$2:A2,A2,$B$2:B2),"")

for subtotals.

main.xls

main.xls
	B	C	D	E
1	GroupID	Total
2	123	500
3	456	600
4	789	1200
5
Sheet1

C2, copied down:

=IF(LOOKUP(B2,[Data.xls]Sheet1!$A$2:$A$8)=B2,LOOKUP(B2,[Data.xls]Sheet1!$A$2:$A$8,[Data.xls]Sheet1!$C$2:$C$8),"")

The formula reflects the situation when both files are open. When Data.xls is closed, this formula will include the full path to the file.

 

[monometh]

Re: match fetch query between 2 spreadsheets, help appreciat

Hi Aladin,


That's impressive, thanks again, I'll give it a try.

regards