Using Index, Match and Small to Populate Data from a Data Page

N

NateN

New Member
Joined
Apr 4, 2017
Messages
18
Good Day,

I'm trying to figure out how to pull data from my raw data page (Screenshot 1) and transfer certain data to the second spreadsheet (Screenshot 2). I'm having troubles with getting my formula to populate the correct run number.

I'm trying to use Index match function. If I select company 1 then it should populate all of company 1's transactions. But when company one has more than 1 transaction on the same day, the formula doesn't seem to pick up the next #.

I've been playing around with the formula but I can't seem to get it to work.

Any help would be greatly appreciated.


Excel 2010
A
B
C
D
E
F
1
Transferor
Transferee
Date
Transfer
Deemed Assets
Deemed Liabilities
2
1
2
28-Apr-17
1234567
$0.00
($72,526.00)
3
2
1
28-Apr-17
1234568
$0.00
$72,526.00
4
3
6
25-Apr-17
1234569
$0.00
$0.00
5
4
15
25-Apr-17
1234570
$0.00
$0.00
6
4
5
25-May-17
1234571
($500.00)
($1,000.00)
7
5
4
5-Apr-17
1234572
$0.00
$0.00
8
1
2
29-Apr-17
1234573
$500.00
($500.00)
9
1
8
29-Apr-17
1234574
$500.00
($500.00)

<tbody>
</tbody>
Sheet1​





Excel 2010
A
B
3
1
4
Date
Run #
10
28-Apr-17
1234567
11
29-Apr-17
1234573
12
29-Apr-17
1234573

<tbody>
</tbody>
Sheet3​


Array Formulas
Cell
Formula
A10
{=IFERROR(INDEX(Sheet1!$C$2:$C$8, SMALL(IF((Sheet1!$A$2:$A$37=$A$3)*(Sheet1!$G$2:$G$37=Sheet3!$D$3),ROW(Sheet1!$C$2:$C$37)-MIN(ROW(Sheet1!$C$2:$C$37))+1),ROWS(A$10:A10)),1),"")}
B10
{=IFERROR(INDEX(Sheet1!$A$2:$F$9,SMALL(IF((Sheet1!$A$2:$A$8=Sheet3!$A$3)*(Sheet1!$C$2:$C$8=Sheet3!A10),ROW(Sheet1!$D$2:$D$8)-MIN(ROW(Sheet1!$D$2:$D$8))+1),ROWS(2:2)),4),"")}
A11
{=IFERROR(INDEX(Sheet1!$C$2:$C$9, SMALL(IF((Sheet1!$A$2:$A$37=$A$3)*(Sheet1!$G$2:$G$37=Sheet3!$D$3),ROW(Sheet1!$C$2:$C$37)-MIN(ROW(Sheet1!$C$2:$C$37))+1),ROWS(A$10:A11)),1),"")}
B11
{=IFERROR(INDEX(Sheet1!$A$2:$F$9,SMALL(IF((Sheet1!$A$2:$A$8=Sheet3!$A$3)*(Sheet1!$C$2:$C$8=Sheet3!A11),ROW(Sheet1!$D$2:$D$8)-MIN(ROW(Sheet1!$D$2:$D$8))+1),ROWS(3:3)),4),"")}
A12
{=IFERROR(INDEX(Sheet1!$C$2:$C$9, SMALL(IF((Sheet1!$A$2:$A$37=$A$3)*(Sheet1!$G$2:$G$37=Sheet3!$D$3),ROW(Sheet1!$C$2:$C$37)-MIN(ROW(Sheet1!$C$2:$C$37))+1),ROWS(A$10:A12)),1),"")}
B12
{=IFERROR(INDEX(Sheet1!$A$2:$F$9,SMALL(IF((Sheet1!$A$2:$A$8=Sheet3!$A$3)*(Sheet1!$C$2:$C$8=Sheet3!A12),ROW(Sheet1!$D$2:$D$8)-MIN(ROW(Sheet1!$D$2:$D$8))+1),ROWS(4:4)),4),"")}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Try this:


Excel 2010
ABCDEF
1TransferorTransfereeDateTransferDeemed AssetsDeemed Liabilities
21228-Apr-171234567$0.00($72,526.00)
32128-Apr-171234568$0.00$72,526.00
43625-Apr-171234569$0.00$0.00
541525-Apr-171234570$0.00$0.00
64525-May-171234571($500.00)($1,000.00)
7545-Apr-171234572$0.00$0.00
81229-Apr-171234573$500.00($500.00)
91829-Apr-171234574$500.00($500.00)
Sheet1



Excel 2010
AB
31
4DateRun #
528-Apr-171234567
628-Apr-171234568
729-Apr-171234573
829-Apr-171234574
Sheet3
Cell Formulas
RangeFormula
A5{=IFERROR(INDEX(Sheet1!C$2:C$9,SMALL(IF(Sheet1!$A$2:$B$9=$A$3,ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.


Drag the formula to the right one column and then down as far as needed. You will have to adjust the formatting to show the dates/numbers properly.
 
Upvote 0
Thanks for your help! I guess I was using MINROW and Rows wrong :(. I appreciate all your help! It makes sense why you did that.
 
Upvote 0
Thanks for your help again. I was wondering if you could help me out with something else related.

If a company is in the "Transferee" Column is there a way to get that value when shown on Sheet 3 to display the opposite sign? In the screen shot provided. Row 6 should have a negative sign instead of a positive.

Can I add another criteria to the formula or is this now getting too complex?

Thanks again in advance

Excel 2010
ABCD
21Month April
3DateTransfer/Run #AssetsLiabilites
4
528-Apr-171234567 - (72,526.00)
627-Apr-171234568 - 72,526.00
729-Apr-171234573 500.00 (500.00)
829-Apr-171234574 500.00 (500.00)

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3​

Array Formulas
CellFormula
A5{=IFERROR(INDEX(Sheet1!C$2:C$9,SMALL(IF((Sheet1!$A$2:$B$9=$A$2)*(Sheet1!$G$2:$G$9=$D$2),ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A1))),"")}
B5{=IFERROR(INDEX(Sheet1!D$2:D$9,SMALL(IF((Sheet1!$A$2:$B$9=$A$2)*(Sheet1!$G$2:$G$9=$D$2),ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A1))),"")}
C5{=IFERROR(INDEX(Sheet1!E$2:E$9,SMALL(IF((Sheet1!$A$2:$B$9=$A$2)*(Sheet1!$G$2:$G$9=$D$2),ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A1))),"")}
D5{=IFERROR(INDEX(Sheet1!F$2:F$9,SMALL(IF((Sheet1!$A$2:$B$9=$A$2)*(Sheet1!$G$2:$G$9=$D$2),ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A1))),"")}
A6{=IFERROR(INDEX(Sheet1!C$2:C$9,SMALL(IF((Sheet1!$A$2:$B$9=$A$2)*(Sheet1!$G$2:$G$9=$D$2),ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A2))),"")}
B6{=IFERROR(INDEX(Sheet1!D$2:D$9,SMALL(IF((Sheet1!$A$2:$B$9=$A$2)*(Sheet1!$G$2:$G$9=$D$2),ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A2))),"")}
C6{=IFERROR(INDEX(Sheet1!E$2:E$9,SMALL(IF((Sheet1!$A$2:$B$9=$A$2)*(Sheet1!$G$2:$G$9=$D$2),ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A2))),"")}
D6{=IFERROR(INDEX(Sheet1!F$2:F$9,SMALL(IF((Sheet1!$A$2:$B$9=$A$2)*(Sheet1!$G$2:$G$9=$D$2),ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A2))),"")}
A7{=IFERROR(INDEX(Sheet1!C$2:C$9,SMALL(IF((Sheet1!$A$2:$B$9=$A$2)*(Sheet1!$G$2:$G$9=$D$2),ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A3))),"")}
B7{=IFERROR(INDEX(Sheet1!D$2:D$9,SMALL(IF((Sheet1!$A$2:$B$9=$A$2)*(Sheet1!$G$2:$G$9=$D$2),ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A3))),"")}
C7{=IFERROR(INDEX(Sheet1!E$2:E$9,SMALL(IF((Sheet1!$A$2:$B$9=$A$2)*(Sheet1!$G$2:$G$9=$D$2),ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A3))),"")}
D7{=IFERROR(INDEX(Sheet1!F$2:F$9,SMALL(IF((Sheet1!$A$2:$B$9=$A$2)*(Sheet1!$G$2:$G$9=$D$2),ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A3))),"")}
A8{=IFERROR(INDEX(Sheet1!C$2:C$9,SMALL(IF((Sheet1!$A$2:$B$9=$A$2)*(Sheet1!$G$2:$G$9=$D$2),ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A4))),"")}
B8{=IFERROR(INDEX(Sheet1!D$2:D$9,SMALL(IF((Sheet1!$A$2:$B$9=$A$2)*(Sheet1!$G$2:$G$9=$D$2),ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A4))),"")}
C8{=IFERROR(INDEX(Sheet1!E$2:E$9,SMALL(IF((Sheet1!$A$2:$B$9=$A$2)*(Sheet1!$G$2:$G$9=$D$2),ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A4))),"")}
D8{=IFERROR(INDEX(Sheet1!F$2:F$9,SMALL(IF((Sheet1!$A$2:$B$9=$A$2)*(Sheet1!$G$2:$G$9=$D$2),ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A4))),"")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Upvote 0
Do you mean like this?


Excel 2010
ABCDEF
1TransferorTransfereeDateTransferDeemed AssetsDeemed Liabilities
21228-Apr-171234567$0.00($72,526.00)
32128-Apr-171234568$0.00$72,526.00
43625-Apr-171234569$0.00$0.00
541525-Apr-171234570$0.00$0.00
64525-May-171234571($500.00)($1,000.00)
7545-Apr-171234572$0.00$0.00
81229-Apr-171234573$500.00($500.00)
91829-Apr-171234574$500.00($500.00)
Sheet1



Excel 2010
ABCD
1
21
3DateTransfer/Run #AssetsLiabilities
428-Apr-1712345670-72526
528-Apr-171234568072526
629-Apr-171234573500-500
729-Apr-171234574500-500
Sheet3
Cell Formulas
RangeFormula
A4{=IFERROR(INDEX(Sheet1!C$2:C$9,SMALL(IF(Sheet1!$A$2:$B$9=$A$2,ROW(Sheet1!$A$2:$A$9)-(ROW(Sheet1!$A$2)-1)),ROWS(Sheet1!$A$1:$A1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Not quite.
In sheet 1, cell F3 it is a positive number. In sheet 3, D5 should be a negative number since Company 1 is in the transferee column in Sheet 1.

I hope that helps?

Thanks
 
Upvote 0
NateN said:
Not quite.
In sheet 1, cell F3 it is a positive number. In sheet 3, D5 should be a negative number since Company 1 is in the transferee column in Sheet 1.

I hope that helps?

Thanks
Click to expand...

Okay, we could easily make this happen but I think that the bigger issue here is why that number is positive in the source data (Sheet1) when all of the other liabilities are negative.
 
Upvote 0
Oh those numbers are just examples. Trying to work out the kinks in the spreadsheet. Essentially, sometimes they will take on a liability in which case it will be a positive value for the one company. If they transferred it away then it will be a negative. If company 1 for example is in the transferee column then the whatever values that are in asset and liabilities should be the opposite sign.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
INDEX Functions with multiple criteria
Replies
5
Views
260
Phuoc
P
O
  • Question
Adding new items to dataset that a UNIQUE function pulls from breaks adjacent columns
Replies
1
Views
150
SunnyAlv
SunnyAlv
S
  • Solved
Problem modifying INDEX formula
Replies
1
Views
179
MountainFog
M
DNapoli
  • Solved
Index and Match Excel Question.
2 3
Replies
24
Views
504
Peter_SSs
Peter_SSs
J
  • Solved
Calculate last 5 soccer team goals using data from another sheet on same workbook.
2
Replies
19
Views
949
felixstraube
F

Forum statistics

Threads
1,215,432
Messages
6,124,858
Members
449,194
Latest member
HellScout

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