Combining specific information from multiple worksheets

[jennylou]

Hey, I am not a pro in excel but I can get around. Thank you in advance for any guidance.

I have 56 worksheets that look like the below except Column B doesn't always have the same ID#. Is there a way I can pull from each worksheet the information based on the ID#?

ex: ID#=6, and from every worksheet with a 6,it will pull all information on that row (I hope that makes since)

worksheet 1:

A	B	C	D	E	F	G	H	I	J	K
7	I D #	HIGH	TOTAL	1-30	31-60	OVER	TERMS	PAYMENTS
8	CREDIT	OWED	60	Disc.	Ppt.	Days Slow
9	6	120,000	120,000	52,000			REG			25
10
11	15	36,667	3,094	1,145	497		N15			72
12
13	17	19,500	18,756		5,097		N30		X
14
15	24		15,445				2/15 N30		X
16		4,347				1/10 N30		X

<tbody>
</tbody>

worksheet 2

A	B	C	D	E	F	G	H	I	J	K
7	I D #	HIGH	TOTAL	1-30	31-60	OVER	TERMS	PAYMENTS
8	CREDIT	OWED	60	Disc.	Ppt.	Days Slow
9	6	264,000	132,000	9,000			REG	X	X
10
11	11	77,200	44,300				N30		X
12
13	15	138,274	126,058				2/10 N60			20
14
15	17	24,000	22,781	5,551			N60			24

<tbody>
</tbody>

 

[jennylou]

I am sorry it will not hold my format

 

[XOR LX]

Hi.

When you say "pull" what exactly do you mean? Do you have some sort of master sheet set-up in which you'd like to return some collated results?

What happens to the data relating to e.g. ID #6 in that master sheet? Is it all summed/averaged/etc. into a single row? Or do you wish to simply return all rows precisely as they are found in the separate sheets into the master sheet, with no calculation whatsoever performed on that data?

Will this only apply to a single ID at a time?

Please try to be a bit clearer about what it is you actually require.

Regards

 

[jennylou]

XOR LX, I apologize for leaving important information out. I need to set up a master sheet for each ID# to pull from all worksheets if applicable a specific ID# and have it pull that row/rows only. No calculations or anything fancy. (If 20 worksheets have ID# 25, I need a master worksheet that shows all row information for #25 for those 20 worksheets) I hope that helps

Thanks
Jenn

Hi.

When you say "pull" what exactly do you mean? Do you have some sort of master sheet set-up in which you'd like to return some collated results?

What happens to the data relating to e.g. ID #6 in that master sheet? Is it all summed/averaged/etc. into a single row? Or do you wish to simply return all rows precisely as they are found in the separate sheets into the master sheet, with no calculation whatsoever performed on that data?

Will this only apply to a single ID at a time?

Please try to be a bit clearer about what it is you actually require.

Regards

 

[XOR LX]

It's a job which is far simpler and amenable to VBA, but if for whatever reason you want to try a purely formula-based solution, you could have a go at adapting the solution I outline here:

Collating from multiple sheets based on conditions | EXCELXOR

If you're not sure about anything and/or you'd prefer to go down the VBA route then let me know.

Regards

 

[jennylou]

I was afraid VBA was going to be the route, but I am no where close to be able to do that. I can start a project and rename worksheets within but that is all I know how to do. (I may need to set up a class for that) In the mean time i will frighteningly look at your attachment and see what I can achieve

Thank you for your time and help

It's a job which is far simpler and amenable to VBA, but if for whatever reason you want to try a purely formula-based solution, you could have a go at adapting the solution I outline here:

Collating from multiple sheets based on conditions | EXCELXOR

If you're not sure about anything and/or you'd prefer to go down the VBA route then let me know.

Regards