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

[NateN]

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
Transferor	Transferee	Date	Transfer	Deemed Assets	Deemed Liabilities

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: center"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]28-Apr-17
[/TD]
[TD="align: right"]1234567
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]($72,526.00)
[/TD]

[TD="align: center"]3
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]28-Apr-17
[/TD]
[TD="align: right"]1234568
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$72,526.00
[/TD]

[TD="align: center"]4
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]25-Apr-17
[/TD]
[TD="align: right"]1234569
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]

[TD="align: center"]5
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]15
[/TD]
[TD="align: right"]25-Apr-17
[/TD]
[TD="align: right"]1234570
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]25-May-17
[/TD]
[TD="align: right"]1234571
[/TD]
[TD="align: right"]($500.00)
[/TD]
[TD="align: right"]($1,000.00)
[/TD]

[TD="align: center"]7
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]5-Apr-17
[/TD]
[TD="align: right"]1234572
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0.00
[/TD]

[TD="align: center"]8
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]29-Apr-17
[/TD]
[TD="align: right"]1234573
[/TD]
[TD="align: right"]$500.00
[/TD]
[TD="align: right"]($500.00)
[/TD]

[TD="align: center"]9
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]29-Apr-17
[/TD]
[TD="align: right"]1234574
[/TD]
[TD="align: right"]$500.00
[/TD]
[TD="align: right"]($500.00)
[/TD]

</tbody>

Sheet1​

Excel 2010

	A	B
1
28-Apr-17	1234567
29-Apr-17	1234573
29-Apr-17	1234573

<tbody>
[TD="align: center"]3
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4
[/TD]
[TD="bgcolor: #F2F2F2"]Date
[/TD]
[TD="bgcolor: #F2F2F2"]Run #
[/TD]

[TD="align: center"]10
[/TD]

[TD="align: center"]11
[/TD]

[TD="align: center"]12
[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A10
[/TH]
[TD="align: left"]{=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),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B10
[/TH]
[TD="align: left"]{=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),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A11
[/TH]
[TD="align: left"]{=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),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B11
[/TH]
[TD="align: left"]{=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),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A12
[/TH]
[TD="align: left"]{=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),"")}
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B12
[/TH]
[TD="align: left"]{=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),"")}
[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[NateN]

Any formula changes that someone can suggest?

Thank you

 

[63falcondude]

Try this:

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)
Sheet1

Excel 2010
	A	B
3	1
4	Date	Run #
5	28-Apr-17	1234567
6	28-Apr-17	1234568
7	29-Apr-17	1234573
8	29-Apr-17	1234574
Sheet3

Cell Formulas
Range	Formula
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.

 

[NateN]

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.

 

[63falcondude]

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.

You're welcome. Happy to help.

 

[NateN]

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

	A	B	C	D
1	Month	April

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #F2F2F2"]Date[/TD]
[TD="bgcolor: #F2F2F2"]Transfer/Run #[/TD]
[TD="bgcolor: #F2F2F2, align: right"]Assets[/TD]
[TD="bgcolor: #F2F2F2, align: right"]Liabilites [/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]28-Apr-17[/TD]
[TD="align: right"]1234567[/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] (72,526.00)[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]27-Apr-17[/TD]
[TD="align: right"]1234568[/TD]
[TD="align: right"] - [/TD]
[TD="align: right"] 72,526.00 [/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]29-Apr-17[/TD]
[TD="align: right"]1234573[/TD]
[TD="align: right"] 500.00 [/TD]
[TD="align: right"] (500.00)[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]29-Apr-17[/TD]
[TD="align: right"]1234574[/TD]
[TD="align: right"] 500.00 [/TD]
[TD="align: right"] (500.00)[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A5[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B5[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C5[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D5[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A6[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B6[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C6[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D6[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A7[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B7[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C7[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D7[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]A8[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B8[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C8[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D8[/TH]
[TD="align: left"]{=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))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[63falcondude]

Do you mean like this?

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)
Sheet1

Excel 2010
	A	B	C	D
1
2	1
3	Date	Transfer/Run #	Assets	Liabilities
4	28-Apr-17	1234567	0	-72526
5	28-Apr-17	1234568	0	72526
6	29-Apr-17	1234573	500	-500
7	29-Apr-17	1234574	500	-500
Sheet3

Cell Formulas
Range	Formula
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.

 

[NateN]

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

 

[63falcondude]

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

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.

 

[NateN]

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.