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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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

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

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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

New Member
Joined
Sep 17, 2014
Messages
4
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,601
Messages
5,838,300
Members
430,537
Latest member
Antonio11

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
Top