Combining specific information from multiple worksheets

jennylou

New Member
Joined
Sep 17, 2014
Messages
4
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>
 
Last edited:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,216,126
Messages
6,129,021
Members
449,480
Latest member
yesitisasport

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top