Trying to present a cut down table using VLookups

[whirlwind147]

Hi,


Not really sure the best way to describe what I need to do but I'll give it a go.


I have a stock listing for one of our customers of all we hold for them and they want to see a cut down version of this on a different sheet in the same workbook but only items that meet certain criteria. I've sorted out the formula I need to highlight just these items on the original sheet but I'm having trouble listing the different items on a separate sheet. I've used a VLookup but (as most of you will know being experts) it's picking up the same entry again and again as the identifier on the main sheet is the same.

I've currently got this formula on a few rows:
=VLOOKUP("BI!",'ProdSchedRep'!$A$1:$X$100,2)
And it's picking up the same row each time from the other sheet. What would I need to do to this to get it to show each row with the same identifier? I'm guessing that VLookup isn't the right formula to try but I don't know what is. I've tried to search the forums but not knowing the right formula I do need to use I'm finding it a struggle to search!

If more info is needed then let me know what you need and I'll supply
Thanks for any help you can give.

 

[Special-K99]

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

Attachments

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

 

[whirlwind147]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Full Table Example:
2	Ref Only	Del Date	Despatch Date	Despatch Method	Booked on System?	Booking in Date	Our Ref	Our Order No	Cust Order No	Item No	Job Ref	Qty Ord	Qty Del	No tickets produced to date	Stock	Tickets in Hand	Awaiting No Of Tickets
3	999	21/07/2017	21/07/2017	DC T	?	21/07/2017	17/12345	11111	WE1111	123456	Job Ref 1	170	0	47	170	47	123
4	BI!	21/07/2017	21/07/2017	DC T	?	21/07/2017	17/23456	22222	WE2222	234567	Job Ref 2	130	47	60	83	13	70
5	999	04/09/2017	04/09/2017	EW	?	04/09/2017	17/34567	33333	WE3333	345678	Job Ref 3	80	0	0	80	0	80
6	999	04/09/2017	04/09/2017	EW	?	04/09/2017	17/45678	44444	WE4444	456789	Job Ref 4	300	47	95	253	48	205
7	999	04/08/2017	04/08/2017	DD 3P	?	04/08/2017	17/56789	55555	WE5555	987654	Job Ref 5	500	0	0	500	0	500
8	BI!	28/08/2017	28/08/2017	DD 3P	?	28/08/2017	17/67890	66666	WE6666	654321	Job Ref 6	260	47	73	213	26	187
9	BI!	07/08/2017	07/08/2017	DC T	?	07/08/2017	17/01234	77777	WE7777	654789	Job Ref 7	2	1	1	1	0	1
10
11	Cut Down:
12	Ref Only	Del Date	Cust Order No	Item Number	Job Ref	Qty Ord
13	BI!	42954	WE7777	654789	Job Ref 7	2
14	BI!	42954	WE7777	654789	Job Ref 7	2
15	BI!	42954	WE7777	654789	Job Ref 7	2
16	BI!	42954	WE7777	654789	Job Ref 7	2
17	BI!	42954	WE7777	654789	Job Ref 7	2
18	BI!	42954	WE7777	654789	Job Ref 7	2
Sheet1

 

[whirlwind147]

I hope that's OK

 

[Special-K99]

Yep looks good.

in Sheet2!A2
=IFERROR(INDEX(Sheet1!$A$3:$Q$9,SMALL(IF((Sheet1!$D$3:$D$9="DCT")*(Sheet1!$M3<>0),ROW($A$3:$A$9)),ROW(A1))-(ROW(A$3)-1),1),"")

in Sheet2!B2
=IFERROR(INDEX(Sheet1!$A$3:$Q$9,SMALL(IF((Sheet1!$D$3:$D$9="DCT")*(Sheet1!$M3<>0),ROW($A$3:$A$9)),ROW(A1))-(ROW(A$3)-1),2),"")

in Sheet2!C2
=IFERROR(INDEX(Sheet1!$A$3:$Q$9,SMALL(IF((Sheet1!$D$3:$D$9="DCT")*(Sheet1!$M3<>0),ROW($A$3:$A$9)),ROW(A1))-(ROW(A$3)-1),9),"")

in Sheet2!D2
=IFERROR(INDEX(Sheet1!$A$3:$Q$9,SMALL(IF((Sheet1!$D$3:$D$9="DCT")*(Sheet1!$M3<>0),ROW($A$3:$A$9)),ROW(A1))-(ROW(A$3)-1),10),"")

in Sheet2!E2
=IFERROR(INDEX(Sheet1!$A$3:$Q$9,SMALL(IF((Sheet1!$D$3:$D$9="DCT")*(Sheet1!$M3<>0),ROW($A$3:$A$9)),ROW(A1))-(ROW(A$3)-1),11),"")

in Sheet2!F2
=IFERROR(INDEX(Sheet1!$A$3:$Q$9,SMALL(IF((Sheet1!$D$3:$D$9="DCT")*(Sheet1!$M3<>0),ROW($A$3:$A$9)),ROW(A1))-(ROW(A$3)-1),12),"")

NOTE: These are ALL array formulas so enter them using Ctrl-Shift-Enter
Once entered in row 2 copy them down the column for as many rows as you have in Sheet1.
In this case your range in Sheet1 is A3:A9 which is 7 rows so copy the formulas down on Sheet 2 for 7 rows.
If you have 1000 rows on your proper "Sheet1" you will need to copy the formulas down 1000 rows on Sheet2.

 

[whirlwind147]

Thanks Special-K99, I shall give this a go and come back here to let you know the result

 

[whirlwind147]

I can't get it to show anything but in my haste I think I may have forgotten to tell you a piece of information though.

In the cut down version I only want to show the orders that have BI! in the very first column in the larger table. The BI! only shows in the larger table when the despatch method is DC T or DD 3P and that the Qty Delivered doesn't say 0.

Sorry I forgot to put that bit :-/

 

[Special-K99]

In my haste i mistook "DC T" for "DCT", you'll need to amend the formula to account for that.
Try this
Again Array formula, use Ctrl-Shift-Enter copy themown the columns

in Sheet2!A2
=IFERROR(INDEX(Sheet1!$A$3:$Q$9,SMALL(IF((Sheet1!$D$3:$D$9="DC T")*(Sheet1!$M3<>0)*(Sheet1!$A3="BI!"),ROW($A$3:$A$9)),ROW(A1))-(ROW(A$3)-1),1),"")

in Sheet2!B2
=IFERROR(INDEX(Sheet1!$A$3:$Q$9,SMALL(IF((Sheet1!$D$3:$D$9="DC T")*(Sheet1!$M3<>0)*(Sheet1!$A3="BI!"),ROW($A$3:$A$9)),ROW(A1))-(ROW(A$3)-1),2),"")

in Sheet2!C2
=IFERROR(INDEX(Sheet1!$A$3:$Q$9,SMALL(IF((Sheet1!$D$3:$D$9="DC T")*(Sheet1!$M3<>0)*(Sheet1!$A3="BI!"),ROW($A$3:$A$9)),ROW(A1))-(ROW(A$3)-1),9),"")

in Sheet2!D2
=IFERROR(INDEX(Sheet1!$A$3:$Q$9,SMALL(IF((Sheet1!$D$3:$D$9="DC T")*(Sheet1!$M3<>0)*(Sheet1!$A3="BI!"),ROW($A$3:$A$9)),ROW(A1))-(ROW(A$3)-1),10),"")

in Sheet2!E2
=IFERROR(INDEX(Sheet1!$A$3:$Q$9,SMALL(IF((Sheet1!$D$3:$D$9="DC T")*(Sheet1!$M3<>0)*(Sheet1!$A3="BI!"),ROW($A$3:$A$9)),ROW(A1))-(ROW(A$3)-1),11),"")

in Sheet2!F2
=IFERROR(INDEX(Sheet1!$A$3:$Q$9,SMALL(IF((Sheet1!$D$3:$D$9="DC T")*(Sheet1!$M3<>0)*(Sheet1!$A3="BI!"),ROW($A$3:$A$9)),ROW(A1))-(ROW(A$3)-1),12),"")

If this doesn't work post a sample file

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

Attachments

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

 

[whirlwind147]

Hi Special-K99

Sorry I have had some normal work to do so had to leave these for a couple of days. I've entered the formula's and I've achieved partial success. The main objective was I wanted the cut down version of the sheet to only pick up the rows with BI! one after the other (so no blank rows). Also something weird is happening with the results. If they all say BI! then they all show up which is great but if one of them is empty then it seems that one or more rows gets ignored. I'll paste an example in the next message.

Thanks for all your help this far by the way

 

[whirlwind147]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Full Table Example:
2	Ref Only	Del Date	Despatch Date	Despatch Method	Booked on System?	Booking in Date	Our Ref	Our Order No	Cust Order No	Item No	Job Ref	Qty Ord	Qty Del	No tickets produced to date	Stock	Tickets in Hand	Awaiting No Of Tickets
3	BI!	21/07/2017	21/07/2017	DC T	?	21/07/2017	17/12345	11111	WE1111	123456	Job Ref 1	170	10	47	160	37	123
4	BI!	21/07/2017	21/07/2017	DC T	?	21/07/2017	17/23456	22222	WE2222	234567	Job Ref 2	130	10	60	120	50	70
5	BI!	04/09/2017	04/09/2017	DC T	?	04/09/2017	17/34567	33333	WE3333	345678	Job Ref 3	80	10	5	70	-5	75
6	999	04/09/2017	04/09/2017	EW	?	04/09/2017	17/45678	44444	WE4444	456789	Job Ref 4	300	10	95	290	85	205
7	BI!	04/08/2017	04/08/2017	DC T	?	04/08/2017	17/56789	55555	WE5555	987654	Job Ref 5	500	10	5	490	-5	495
8	999	28/08/2017	28/08/2017	EW	?	28/08/2017	17/67890	66666	WE6666	654321	Job Ref 6	260	10	73	250	63	187
9	BI!	07/08/2017	07/08/2017	DC T	?	07/08/2017	17/01234	77777	WE7777	654789	Job Ref 7	2	10	1	-8	-9	1
10
11	Cut Down:
12	Ref Only	Del Date	Cust Order No	Item Number	Job Ref	Qty Ord
13	BI!	42937	WE1111	123456	Job Ref 1	170
14	BI!	42937	WE2222	234567	Job Ref 2	130
15	BI!	42982	WE3333	345678	Job Ref 3	80
16
17	BI!	42954	WE7777	654789	Job Ref 7	2
18
19
Sheet1