Formula for returning values from data

[notkaran]

Hi,

I have a data like this

[TABLE="width: 886"]
<tbody>[TR]
[TD]SWOD14658
[/TD]
[TD]GRN Ref. SWOD14658[/TD]
[TD]POW001[/TD]
[TD]POWA KADDY[/TD]
[TD]A03[/TD]
[TD]CART BAGS[/TD]
[TD]00808-01-01[/TD]
[/TR]
[TR]
[TD]SWOD14658[/TD]
[TD]GRN Ref. SWOD14658[/TD]
[TD]POW001[/TD]
[TD]POWA KADDY[/TD]
[TD]B01[/TD]
[TD]MANUAL CADDY CARS[/TD]
[TD]00582-02-01[/TD]
[/TR]
[TR]
[TD]SWOD14658[/TD]
[TD]GRN Ref. SWOD14658[/TD]
[TD]POW001[/TD]
[TD]POWA KADDY[/TD]
[TD]B02[/TD]
[TD]MOTORISED CADDY CARS[/TD]
[TD]00300-104-01[/TD]
[/TR]
[TR]
[TD]SWOD14658[/TD]
[TD]GRN Ref. SWOD14658[/TD]
[TD]POW001[/TD]
[TD]POWA KADDY[/TD]
[TD]B02[/TD]
[TD]MOTORISED CADDY CARS[/TD]
[TD]00700-108-01[/TD]
[/TR]
[TR]
[TD]SWOD14658[/TD]
[TD]GRN Ref. SWOD14658[/TD]
[TD]POW001[/TD]
[TD]POWA KADDY[/TD]
[TD]B02[/TD]
[TD]MOTORISED CADDY CARS[/TD]
[TD]00700-120-01[/TD]
[/TR]
[TR]
[TD]SWOD14658 Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SWOD14659[/TD]
[TD]GRN Ref. SWOD14659[/TD]
[TD]DFS001[/TD]
[TD]DF SPORTS[/TD]
[TD]C01[/TD]
[TD]SETS OF CLUBS[/TD]
[TD]SPALELITESTD[/TD]
[/TR]
[TR]
[TD]SWOD14659 Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SWOD14660[/TD]
[TD]GRN Ref. SWOD14660[/TD]
[TD]MAS001[/TD]
[TD]masters golf[/TD]
[TD]C10[/TD]
[TD]JUNIOR SETS[/TD]
[TD]SETMKL53[/TD]
[/TR]
[TR]
[TD]SWOD14660[/TD]
[TD]GRN Ref. SWOD14660[/TD]
[TD]MAS001[/TD]
[TD]masters golf[/TD]
[TD]C10[/TD]
[TD]JUNIOR SETS[/TD]
[TD]SETMKL57[/TD]
[/TR]
[TR]
[TD]SWOD14660 Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SWOE01575[/TD]
[TD]GRN Ref. SWOE01575[/TD]
[TD]PIN002[/TD]
[TD]PING EUROPE LTD[/TD]
[TD]H02[/TD]
[TD]GLOVES[/TD]
[TD]881978130384[/TD]
[/TR]
[TR]
[TD]SWOE01575 Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SWOE01576[/TD]
[TD]GRN Ref. SWOE01576[/TD]
[TD]OAK001[/TD]
[TD]OAKLEY[/TD]
[TD]F08[/TD]
[TD]SWEAT/FLEECE[/TD]
[TD]432837-762-L[/TD]
[/TR]
[TR]
[TD]SWOE01576[/TD]
[TD]GRN Ref. SWOE01576[/TD]
[TD]OAK001[/TD]
[TD]OAKLEY[/TD]
[TD]F08[/TD]
[TD]SWEAT/FLEECE[/TD]
[TD]432837-762-M[/TD]
[/TR]
[TR]
[TD]SWOE01576[/TD]
[TD]GRN Ref. SWOE01576[/TD]
[TD]OAK001[/TD]
[TD]OAKLEY[/TD]
[TD]F08[/TD]
[TD]SWEAT/FLEECE[/TD]
[TD]432837-762-S[/TD]
[/TR]
[TR]
[TD]SWOE01576[/TD]
[TD]GRN Ref. SWOE01576[/TD]
[TD]OAK001[/TD]
[TD]OAKLEY[/TD]
[TD]F08[/TD]
[TD]SWEAT/FLEECE[/TD]
[TD]432837-762-XL[/TD]
[/TR]
</tbody>[/TABLE]



What I would like to do is fetch the entries for, lets say SWOD14658 in a different sheet. I would require all the columns and all the rows for a particular GRN.

Since this data doesn't have any unique value, vlookup wouldn't work.

Any help would be appreciated.

 

[MarcelBeug]

Assuming data is in Sheet1 row 1:1000.

In the other sheet, let:
B1 is the GRN number to be selected
B2 is the number of matching rows: =COUNTIF(Sheet1!$A$1:$A$1000,$B$1)

C is a helper column with indices of selected GRN's on Sheet1.
C1, array formula, confirm with Ctrl+Shift+Enter, not just Enter and copied down for as many rows you would possibly require for 1 GRN:

=IF(ROWS(C$1:C1)>$B$2,"",SMALL(IF(Sheet1!$A$1:$A$1000=$B$1,ROW(Sheet1!$A$1:$A$1000)-ROW(Sheet1!$A$1)+1),ROWS(C$1:C1)))

D1 =IF($C1="","",INDEX(Sheet1!A$1:A$1000,$C1))
copy to the right thru J1 and down for as many rows you would possibly require for 1 GRN.

This won't include the total rows.

 

[notkaran]

Assuming data is in Sheet1 row 1:1000.

In the other sheet, let:
B1 is the GRN number to be selected
B2 is the number of matching rows: =COUNTIF(Sheet1!$A$1:$A$1000,$B$1)

C is a helper column with indices of selected GRN's on Sheet1.
C1, array formula, confirm with Ctrl+Shift+Enter, not just Enter and copied down for as many rows you would possibly require for 1 GRN:

=IF(ROWS(C$1:C1)>$B$2,"",SMALL(IF(Sheet1!$A$1:$A$1000=$B$1,ROW(Sheet1!$A$1:$A$1000)-ROW(Sheet1!$A$1)+1),ROWS(C$1:C1)))

D1 =IF($C1="","",INDEX(Sheet1!A$1:A$1000,$C1))
copy to the right thru J1 and down for as many rows you would possibly require for 1 GRN.

This won't include the total rows.

Hi, thanks for your time,
It somewhat worked for me, I'll keep on trying until its what I want it to be, I really appreciate your reply.
Could you explain me what exactly we did in the formula?

Karan

 

[MarcelBeug]

Could you explain me what exactly we did in the formula?

Sure.

C1:
The first part checks if there are any more entries. Going down the rows, ROWS(C$1:C1) is 1 on row 1, 2 on row 2 (ROWS(C$1:C2)) and so on. So if you have 4 lines for a specific GRN (the value in $B$2), lines 5 and down will be empty.

The part starting with SMALL, looks which lines in Sheet1 have the GRN number you are looking for, and returns the first one (or smallest index) on line 1, the second smallest on line 2 etcetera:
Sheet1!$A$1:$A$1000=$B$1 returns an array with FALSEs and TRUEs indicating if the values in column A equal the GRN you are looking for (which is in B$1$),
ROW(Sheet1!$A$1:$A$1000)-ROW(Sheet1!$A$1)+1 returns the values 1, 2, etcetera... ,1000. (This will still work if you would enter lines above line 1).
ROWS(C$1:C1) is the second argument of the SMALL function, returning 1 on line 1, 2 on line 2 etcetera.

Example: if your GRN number is on rows 4 and 5 in Sheet1, this is evaluated on row1 of your results sheet as:
SMALL(IF({FALSE, FALSE, FALSE, TRUE, TRUE, FALSE (x995)}, {1, 2, 3, 4, 5, etcetera thru 1000}), 1) =
SMALL({FALSE, FALSE, FALSE, 4, 5, FALSE (x995)},1) =
4

D1:
Returns the value from Sheet1!A$1:A$1000, row $C1. When dragged to the right, also the Sheet1 column will shift to B, C etcetera.

 

[notkaran]

Assuming data is in Sheet1 row 1:1000.

In the other sheet, let:
B1 is the GRN number to be selected
B2 is the number of matching rows: =COUNTIF(Sheet1!$A$1:$A$1000,$B$1)

C is a helper column with indices of selected GRN's on Sheet1.
C1, array formula, confirm with Ctrl+Shift+Enter, not just Enter and copied down for as many rows you would possibly require for 1 GRN:

=IF(ROWS(C$1:C1)>$B$2,"",SMALL(IF(Sheet1!$A$1:$A$1000=$B$1,ROW(Sheet1!$A$1:$A$1000)-ROW(Sheet1!$A$1)+1),ROWS(C$1:C1)))

D1 =IF($C1="","",INDEX(Sheet1!A$1:A$1000,$C1))
copy to the right thru J1 and down for as many rows you would possibly require for 1 GRN.

This won't include the total rows.

Hi,
It went amazing, almost what I wanted it to be, Thanks to you. However I am stuck with one thing, first I followed your example and it was great, when I implemented this in my working I am getting #VALUE! error if I copy the formula to the rows greater than countif value.

Where am I going wrong?

Here are the formulas I am using,
=IF(ROWS(A$10:A12)>$B$9," ",SMALL(IF('[grn detail for matching Invoices.xls]Details'!$A:$A=$A$1,ROW('[grn detail for matching Invoices.xls]Details'!$A:$A)-ROW('[grn detail for matching Invoices.xls]Details'!$A$1)+1),ROWS(A$10:A12)))

this one doesn't show #value error.
=IF($A10="","",INDEX('[grn detail for matching Invoices.xls]Details'!A:A,$A10))

this one is showing #value! error, if the formula is copied to more rows than the countif value.

 

[MarcelBeug]

In the first formula, column A gets a space (" ") if the row number is > $B$9, while the second formula checks if the field is empty ("").
Column A should get "" instead of " ".
Otherwise it's really not a good idea to reference entire columns in an array formula as it will really slow down your workbook.
So try and limit your ranges to the maximum ever required.

 

[notkaran]

Sure.

C1:
The first part checks if there are any more entries. Going down the rows, ROWS(C$1:C1) is 1 on row 1, 2 on row 2 (ROWS(C$1:C2)) and so on. So if you have 4 lines for a specific GRN (the value in $B$2), lines 5 and down will be empty.

The part starting with SMALL, looks which lines in Sheet1 have the GRN number you are looking for, and returns the first one (or smallest index) on line 1, the second smallest on line 2 etcetera:
Sheet1!$A$1:$A$1000=$B$1 returns an array with FALSEs and TRUEs indicating if the values in column A equal the GRN you are looking for (which is in B$1$),
ROW(Sheet1!$A$1:$A$1000)-ROW(Sheet1!$A$1)+1 returns the values 1, 2, etcetera... ,1000. (This will still work if you would enter lines above line 1).
ROWS(C$1:C1) is the second argument of the SMALL function, returning 1 on line 1, 2 on line 2 etcetera.

Example: if your GRN number is on rows 4 and 5 in Sheet1, this is evaluated on row1 of your results sheet as:
SMALL(IF({FALSE, FALSE, FALSE, TRUE, TRUE, FALSE (x995)}, {1, 2, 3, 4, 5, etcetera thru 1000}), 1) =
SMALL({FALSE, FALSE, FALSE, 4, 5, FALSE (x995)},1) =
4

D1:
Returns the value from Sheet1!A$1:A$1000, row $C1. When dragged to the right, also the Sheet1 column will shift to B, C etcetera.

Hi,

I have ran into another issue, I can values in the formula only when the source sheet is open, once I close the source sheet and change the GRN it shows #VALUE! error, as soon as I open the source sheet its back to normal. Am I doing anything wrong?

 

[notkaran]

In the first formula, column A gets a space (" ") if the row number is > $B$9, while the second formula checks if the field is empty ("").
Column A should get "" instead of " ".
Otherwise it's really not a good idea to reference entire columns in an array formula as it will really slow down your workbook.
So try and limit your ranges to the maximum ever required.

That's it, it worked, you are awesome.
I shall change the array to maximum required range, thanks for the tip.

 

[notkaran]

Hi, I have resolved my issue, I used SUMPRODUCT instead of COUNTIF.

http://www.mrexcel.com/forum/excel-questions/302354-index-formula-closed-workbooks.html

Thank you MarcelBeug and Aladin Akyurek, you guys are awesome.

 

[notkaran]

Hi again,
If my data is like this

[TABLE="width: 718"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]SWO000700[/TD]
[TD]GRN Ref. SWO000700[/TD]
[TD]POW001[/TD]
[TD]POWA KADDY[/TD]
[TD="align: right"]811280[/TD]
[/TR]
[TR]
[TD]SWO000702[/TD]
[TD]GRN Ref. SWO000702[/TD]
[TD]POW001[/TD]
[TD]POWA KADDY[/TD]
[TD="align: right"]811280[/TD]
[/TR]
[TR]
[TD]SWO000703[/TD]
[TD]GRN Ref. SWO000703[/TD]
[TD]POW001[/TD]
[TD]POWA KADDY[/TD]
[TD="align: right"]811280
[/TD]
[/TR]
</tbody>[/TABLE]


And I want to fetch all the GRNs for 811280, is the procedure same? It doesn't seem to work for me, I am getting 0 as a result.
Or last column has to be the first, like we do in vlookup?

Thanks