Return matching rows from other sheet according to one criteria

S

solti

Board Regular
Joined
May 15, 2015
Messages
52
Hello,

I have 2 Sheets:
In the first sheet in col B I have a unique account number
In the second sheet I have 10 columns. In column 3 I have the same number of in many rows (details on. F-R). It can be up to 5 thousand records in second sheet.

For each account from the first sheet I have to look up a matching number of account in second sheet and copy rows under a searched row from first sheet. It can be ca. 100 unique accounts in first sheet.

Then, for each account in first sheet I have to insert subtotals and grouping.
I have done that with "for... next" implemented many times. For sure to many and takes ages.

Probably better to use an array. Can somebody suggested me a solution with the use of arrays in this example.

Thank you,
s
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Hi S,

How about posting a few of the account numbers from first sheet and a small number of the rows of data (say 10-12) from the second sheet.

That and some details of what would get copied. You don't say where the copies should go, so include that also.

Howard
 
Upvote 0
L. Howard said:
Hi S,

How about posting a few of the account numbers from first sheet and a small number of the rows of data (say 10-12) from the second sheet.

That and some details of what would get copied. You don't say where the copies should go, so include that also.

Howard
Click to expand...


Sheet1
COSTSAccountDataInvoice noClient nameTransactionAmount
I. TOTAL costs-866,00
Account1_name402-009, 402-010, 402-012, 402-023676,00
402-0092016-01-29inv _23Kontr23Text2387,00
402-0092016-01-29inv _25Kontr25Text2585,00
SUM: 402-009172,00
402-0102016-01-16inv _2Kontr2Text262,00
402-0102016-01-21inv _6Kontr6Text684,00
402-0102016-01-27inv _13Kontr13Text1397,00
SUM: 402-010243,00
402-0122016-01-29inv _21Kontr21Text2148,00
SUM: 402-01248,00
402-0232016-01-29inv _22Kontr22Text2292,00
402-0232016-01-29inv _24Kontr24Text2431,00
402-0232016-01-29inv _26Kontr26Text2690,00
SUM: 402-023213,00
Account2_name402-001190,00
402-0012016-01-29inv _1Kontr27Text27190,00
SUM: 402-001190,00

<tbody>
</tbody><colgroup><col><col><col span="2"><col><col><col></colgroup>


Sheet2
Data Invoice noAccount noClient nameClient noTransactionAmount
2016-01-29inv _23402-009Kontr23no_Kontr23Text2387,00
2016-01-29inv _25402-009Kontr25no_Kontr25Text2585,00
2016-01-16inv _2402-010Kontr2no_Kontr2Text262,00
2016-01-21inv _6402-010Kontr6no_Kontr6Text684,00
2016-01-27inv _13402-010Kontr13no_Kontr13Text1397,00
2016-01-29inv _21402-012Kontr21no_Kontr21Text2148,00
2016-01-29inv _22402-023Kontr22no_Kontr22Text2292,00
2016-01-29inv _24402-023Kontr24no_Kontr24Text2431,00
2016-01-29inv _26402-023Kontr26no_Kontr26Text2690,00
2016-01-29inv _1402-001Kontr27no_Kontr27Text27190,00

<tbody>
</tbody><colgroup><col><col><col><col><col span="2"><col></colgroup>


So I need to caopy accounts in Sheet1 from Sheet2
Then insert subtotals and group the rows ( 1 level grouping on subtotals (Sheet1, col B). 2 level grouping on accounts (Sheet1, col A).
Please let me know if you need more info
 
Upvote 0
In Sheet1 I have just rows "Account1_name; 402-009, 402-010, 402-012, 402-023",
"Account2_name; 402-001". So in acoount can be either one or more accounts. For these accounts I need to copy rows from Sheet2

solti said:
Sheet1
COSTS
Account
Data
Invoice no
Client name
Transaction
Amount
I. TOTAL costs
-
866,00
Account1_name
402-009, 402-010, 402-012, 402-023
676,00
402-009
2016-01-29
inv _23
Kontr23
Text23
87,00
402-009
2016-01-29
inv _25
Kontr25
Text25
85,00
SUM: 402-009
172,00
402-010
2016-01-16
inv _2
Kontr2
Text2
62,00
402-010
2016-01-21
inv _6
Kontr6
Text6
84,00
402-010
2016-01-27
inv _13
Kontr13
Text13
97,00
SUM: 402-010
243,00
402-012
2016-01-29
inv _21
Kontr21
Text21
48,00
SUM: 402-012
48,00
402-023
2016-01-29
inv _22
Kontr22
Text22
92,00
402-023
2016-01-29
inv _24
Kontr24
Text24
31,00
402-023
2016-01-29
inv _26
Kontr26
Text26
90,00
SUM: 402-023
213,00
Account2_name
402-001
190,00
402-001
2016-01-29
inv _1
Kontr27
Text27
190,00
SUM: 402-001
190,00

<tbody>
</tbody>


Sheet2
Data
 Invoice no
Account no
Client name
Client no
Transaction
Amount
2016-01-29
inv _23
402-009
Kontr23
no_Kontr23
Text23
87,00
2016-01-29
inv _25
402-009
Kontr25
no_Kontr25
Text25
85,00
2016-01-16
inv _2
402-010
Kontr2
no_Kontr2
Text2
62,00
2016-01-21
inv _6
402-010
Kontr6
no_Kontr6
Text6
84,00
2016-01-27
inv _13
402-010
Kontr13
no_Kontr13
Text13
97,00
2016-01-29
inv _21
402-012
Kontr21
no_Kontr21
Text21
48,00
2016-01-29
inv _22
402-023
Kontr22
no_Kontr22
Text22
92,00
2016-01-29
inv _24
402-023
Kontr24
no_Kontr24
Text24
31,00
2016-01-29
inv _26
402-023
Kontr26
no_Kontr26
Text26
90,00
2016-01-29
inv _1
402-001
Kontr27
no_Kontr27
Text27
190,00

<tbody>
</tbody>


So I need to caopy accounts in Sheet1 from Sheet2
Then insert subtotals and group the rows ( 1 level grouping on subtotals (Sheet1, col B). 2 level grouping on accounts (Sheet1, col A).
Please let me know if you need more info
Click to expand...
 
Upvote 0
could somebody please help


solti said:
In Sheet1 I have just rows "Account1_name; 402-009, 402-010, 402-012, 402-023",
"Account2_name; 402-001". So in acoount can be either one or more accounts. For these accounts I need to copy rows from Sheet2
Click to expand...
 
Upvote 0
So on your Post #4 Sheet 1, it would start off with just columns A and B which are Costs and Accounts and the associated data in those two columns.

And you want to copy from sheet 2 to fill in the sheet 1 columns Data Invoice No. Client Name Transaction Amount which would be columns C, D, E, F, G?

You mention level one and level two, can you explain that some more, please?
Howard
 
Last edited:
Upvote 0
In sheet 1 in col:
- A I have account name (for example: "Account1_name", "Account2_name"...etc)
- B I have account no (for example: 402-009, 402-010, 402-012, 402-023). For each of these accounts I need to copy relevant accounts from Sheet2 and paste them one row below in following way: col A (sheet2) to col C (sheet1), col B(sheet2) to col D (sheet1), col D(sheet2) to col E (sheet1), col F(sheet2) to col F (sheet1), col G(sheet2) to col G (sheet1).

then the subtotals should be: first on each account,, second on account name (which can gather many accounts for example here: Account1_name 402-009, 402-010, 402-012, 402-023
The idea is that I should have just line f.example "Account1_name 402-009, 402-010, 402-012, 402-023 visible with "+" when clicked it breakes down to accounts subtotals rows with "+", when clicked I breaks down further to single invoices rows

If anything is still unclear please ask
s.


L. Howard said:
So on your Post #4 Sheet 1, it would start off with just columns A and B which are Costs and Accounts and the associated data in those two columns.

And you want to copy from sheet 2 to fill in the sheet 1 columns Data Invoice No. Client Name Transaction Amount which would be columns C, D, E, F, G?

You mention level one and level two, can you explain that some more, please?
Howard
Click to expand...
 
Upvote 0
Ponder as I may, I cannot figure a way to make that happen. Enough information, I believe, but don't know how to proceed. Sorry.

Howard
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question
Printing multiple times with changing data
Replies
1
Views
213
orzys
O
R
  • Solved
Using VBA to achieve looping a user input form and consolidate the output into another worksheet
Replies
1
Views
477
Artik
Artik
L
  • Solved
Using IF inside FILTER command to modify a value
Replies
1
Views
308
Ludwig
L
C
  • Question
Comparing multiple rows with multiple details from one sheet to another.
Replies
4
Views
304
cytochrome
C
T
  • Question
Viewing data rows from another worksheet in a portal
Replies
1
Views
127
kevin9999
K

Forum statistics

Threads
1,214,403
Messages
6,119,309
Members
448,886
Latest member
GBCTeacher

Share this page

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