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:

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,543
Messages
5,529,456
Members
409,879
Latest member
Aussie_Excel_Wanna_Be
Top