find corresponding data for a particular date.

[tpsdas]

date shift lot production spoil
1-Aug A 1 10000 1
1-Aug A 2 500 2
1-Aug B 2 2000 3
2-Aug A 1 10000 4
2-Aug B 2 500 5
2-Aug B 2 2000 6
3-Aug A 1 30000 7
3-Aug B 1 50000 8
3-Aug B 2 4000 9
4-Aug A 2 10000 10
4-Aug A 1 500 11
4-Aug B 1 2000 12
5-Aug A 2 10000 13
5-Aug A 2 500 14
5-Aug B 1 2000 15
6-Aug A 1 10000 16
6-Aug A 2 500 17
6-Aug B 2 2000 18

3-Aug A 1 30000 7
3-Aug B 2 4000 9
3-Aug B



Plz ref the above production table ..there are dates & each date has 3 entries ( more that 2) ..with VLOOPUP , i am able to find 2 related & corresponding data for a particular date. But here I want to find for 3 dates ( or more that 2 days)
production data for the related date.
wITH vlookup , I am only able to find date for 2 line. i want to find data for 3 lines for a particular date & shift
We may use Index & match function . Plz help me
QTS IS HOW TO FIND THE 3RD LINE…..I WANT TO FIND FOR MORE THAT 2 LINES.
FOR THE SAME DATES

[oeldere]

post an small excel file, without condidential information.

please also add the desired (expected) result.

[Vikas_Gautam]

Got the solution..
check the attachment..
Non Array solution...

Please Login or Register  to view this content.

Visit this to know more..
Amazing use of countifs and frequency

[sktneer]

Hi tpsda,
Welcome to the Forum.
Please find the attached sheet. In the attached sheet, I have used two methods to achieve the desire output. One is Advanced Filter and Macro and other is Array Formula. See which method suits you.

The array formula used is ......

In O5

Please Login or Register  to view this content.

and then copy across and down in the yellow area or as per your need.

Array Formula requires special keystrokes Ctrl+Shift+Enter instead of just Enter which you normally use in case of a regular excel formula.

[tpsdas]

thanks a lot ...Mr. Vikas Goutam & Mr. Sktneer...it works...& I am happy to get the replies so quickly.
I feel i will use the Array Formula. But I did not understand it. Is it possible to explain the steps the the Array Formula.in details?
Attention mr. sktneer if possible please explain the steps in details.

[tpsdas]

thanks a lot ...Mr. Vikas Goutam & Mr. Sktneer...it works...& I am happy to get the replies so quickly.
I feel i will use the Array Formula. But I did not understand it. Is it possible to explain the steps the the Array Formula.in details?
Attention mr. sktneer if possible please explain the steps in details.

[sktneer]

Details of what, Method1 or Method2?

[tpsdas]

Array Formula - method 2

[sktneer]

OK. You know the syntax of the Index formula. It is Index(array,row_number,column_number). So in the formula you pass a range as an array, a row number and a column number if the array is two dimensional to get the desired value from the specified range in the array part.
Notice the formula in O5, it is

Please Login or Register  to view this content.

In this formula you want to get a value from the range $A$2:$A$19. Since this is one dimensional array, you need to pass only a row number in the index formula.
Now the question comes which row number? Since you want only the row numbers where dates in col. A are equal to the date mentioned in O2. As you see in the col. A that there are multiple rows where the dates are equal to the date in O2. A normal index formula expect you to pass a single row number and it will return the data from the range that belongs to that row number. But here in this case you want to get the data from multiple rows. So in the row_number part of the index formula, you use another function called SMALL and in this case it is

Please Login or Register  to view this content.

And SMALL function [SMALL(array,bin)] returns the smallest value from an array i.e.smalles, 2nd smallest, 3rd smallest etc. Here in the small function in the array part you put a logical condition IF($A$2:$A$19=$O$2 and if it is found true, it returns the array ROW($A$2:$A$19)-ROW($A$2)+1 (which is nothing but an array of row numbers {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18}, remember there are only 18 rows in the array which you passed in the index formula) where the logical condition is found true. So the small function will return 1,2,3 because these are the rows, where the dates are equal to the date in O2. That means you want row1, row2 and row3 from the array $A$2:$A$19. In the bin part of the small function, you use ROWS(O$5:O5) which is equal to 1 in the first formula cell. So in the first cell you get the value of A2 as a result of the index formula. When you drag down the formula in the next row the bin part ROWS(O$5:O5) becomes ROWS(O$5:O6) which is equal to 2 that means you are passing row number 2 in the index formula so you get the second row of the array range in the next cell.
Now the question is what makes this formula as an array formula. It is the IF logical statement where you are comparing a range of values with a single value, that makes it as an array formula.

To understand clearly what the formula is doing use Evaluate feature. Each steps of the formula are as follows. Notice the changes in every step carefully.
I have removed the Iferror part from the formula while explaining the steps.

Step 1. =INDEX(A$2:A$19,SMALL(IF($A$2:$A$19=$O$2,ROW($A$2:$A$19)-ROW($A$2)+1),ROWS(O$5:O5)))

Step 2. =INDEX(A$2:A$19,SMALL(IF({41852;41852;41852;41853;41853;41853;41854;41854;41854;41855;41855;41855;41856;41856;41856;41857;41857;41857}=41852,ROW($A$2:$A$19)-ROW($A$2)+1),ROWS(O$5:O5)))

Step 3. =INDEX(A$2:A$19,SMALL(IF({TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},ROW($A$2:$A$19)-ROW($A$2)+1),ROWS(O$5:O5)))

Step 4. =INDEX(A$2:A$19,SMALL(IF({TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18}),ROWS(O$5:O5)))

Step 5. =INDEX(A$2:A$19,SMALL({1;2;3;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},ROWS(O$5:O5)))

Step 6. =INDEX(A$2:A$19,SMALL({1;2;3;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},1))

Step 7. =INDEX(A$2:A$19,1)

Step 8. =41852 Which a serial number equivalent to 01/08/2014.

Go through each steps several times to understand the formula well.

Hope this helps.

[Vikas_Gautam]

Well explained Sktneer..
Informative..

@TPSDAS
If you wanna learn my formula.. then visit following link....
http://excel-buzz.blogspot.com/2014/...xsmall-as.html

[tpsdas]

thanks a lot ...Mr. Vikas Goutam & Mr. Sktneer
Attention mr. sktneer
1st of all THANKS A LOT. u have done a marvelous job for me..so much help in so little time...once again thanks a ton. But.. Sorry.. I am yet to go through ur details & try to understand. may be on Sunday i will go through.
But some problem I face:
1) any change/correction in DATA in the original table is NOT working ..why ..plz explain &
help..even the original formula is not changed.

Attention Mr. Vikas Goutam
Thanks dear. I will definitely go through Ur help & try to learn more.Index, Match,
Frequency & Small all r new to me ..so will take little time to understand & use it .
For ur help in this case also if i delete the 1st 2 blank columns it is not working. Is it deliberately kept blank.
Sorry Boss!! I am quite ignorant abt those functions u both hv used ..so it may seem to
be a foolish question ..but if possible plz help.

[Vikas_Gautam]

Of course that will give reference error..
I have changed the reference to the rows and columns containing master data.. so that you can never delete them .. hahaha..
Try this..
=INDEX($B$4:$E$21,SMALL(INDEX(COUNTIF($G$2,$A$4:$A$21)*(ROW($A$4:$A$21)-3),),FREQUENCY(COUNTIF($G$2,$A$4:$A$21),0)+ROW(1:1)),COLUMNS($A$1:A1))
check the attachment..

[tpsdas]

Sir .....I need help again.
To sort ..batch & lots...& also add / sub total them. The details. is in the attached file. plz..help me
tapas

[tpsdas]

Dear sktneer ,
The formula was working well. BUt of late I hv found that , when the formula is copied , it is showing errors. Can u plz help me out ? Added the file again fro ur kind perusal.
Regards
tapas

[sktneer]

Your formula in C29 is not an array formula. To check if you have correctly entered a formula as an array formula, click the cell with formula and notice in the formula bar. If you don't see the curly braces around the formula, that means you have not correctly entered the array formula. So while still in the formula cell, press F2 and hold down the Ctrl+Shift together and then press Enter.
In this case, the best way is to correctly enter array formula in B29 and then drag the formula across and down. And then format the columns as desired.

[tpsdas]

dear sktneer,
plz c the attached xls and look at the formula..if wrong plz correct it. test it my copy & drag and plz send me the correct one. I hv as u told still not working. may be I am making sm mistake. plz help.

[tpsdas]

sir i hv tried that...pressing F2 and hold down the Ctrl+Shift+enter. still not working . Noticed..if i copy or correct it for sm reason..& again do Ctrl+Shift+enter ..then also it does not work.

awaiting for ur reply eagerly. this formula u had sent is small & easy..plz help.Added the file again fro ur kind perusal.

[samba_ravi]

There is nothing wrong in the formula
Since you are using Excel 2003 version it will not support IFERROR function
=IF(ISERROR(INDEX(A$8:A$25,SMALL(IF($A$8:$A$25=$A$29,ROW($A$8:$A$25)-ROW($A$8)+1),ROWS(B$29:B29)))),"",INDEX(A$8:A$25,SMALL(IF($A$8:$A$25=$A$29,ROW($A$8:$A$25)-ROW($A$8)+1),ROWS(B$29:B29))))

Use the above in Cell "B29" and copy across
Make sure since it is a array formula press Shift+Ctrl+Enter

For without array try the below

=IF(ISERROR(INDEX(A1:A$25,SMALL(INDEX(($A$8:$A$25<>$A$29)*10^10+ROW($A$8:$A$25),0),ROW(A1)))),"",INDEX(A1:A$25,SMALL(INDEX(($A$8:$A$25<>$A$29)*10^10+ROW($A$8:$A$25),0),ROW(A1))))

[Glenn Kennedy]

The only problem witht he formaul was the error trapping. Since you are using Excel 2003, a different way of removing errors is needed. see the attached.

[tpsdas]

ya..it is working fine.
Only prob is I did not understand it. So i wll not be able to use it further without the help of excel gurus like u ppl.
I kindly request U to EXPLAIN the formula in details Step by Step so as i can learn & use it in other cases also
specially
ROW($A$8:$A$100)-ROW($A$8)+1),ROWS(C$102:C102))
and the part of the formula u hv given
......ROWS(B$29:B29)))),"",INDEX(A$8:A$25,SMALL.........

Regards
Tps

[Glenn Kennedy]

Which of us are you aasking the question in Post 19?

[tpsdas]

ya..it is working fine.Unfortunately I did not understand it. I will just copy it & use it in my sheet . BUt i wll not be able to use it further without the help of excel gurus like u.
I kindly request U to EXPLAIN the formula in details Step by Step so as i can learn & use it in other cases also

[tpsdas]

post 19 solved.
plz explain the step in details sir

[Glenn Kennedy]

Who posted the formula you want explained. Which post is it in????

[tpsdas]

Finding corresponding data (non Repetitive - Lots) from a GIVEN data (batch) - in data table

actually it is another part of the same problem. Hope I can continue here. if not , i will open a new post.
Problem: from a GIVEN batch - Finding Lots ( non Repetitive ) in data table
Requested to look the attached xls sheet fro details
regards
tps

[Vikas_Gautam]

Here is my way.. (Use Formula auditing to understand it more..)
Confirm by using CTRL + SHIFT + ENTER (Not just ENTER)

Please Login or Register  to view this content.

Check the attached file:-

[tpsdas]

thanks..Vikash....thanks a lot for ur help.
kindly remove the small error #NUM!..when batch is blank. plz c the attached xls.

10 22
10 123
10 #NUM!
10 #NUM!

[Vikas_Gautam]

Oh You are using Excel2003, So you can't exploit IFERROR function available in newer excel version.
However in Excel 2003, a simple conditional formatting trick would help.
Here are the steps:-
1. Select the target range. (ie. D130:D150
2. Put a Conditional formatting formula =iserror(D130) (the cell should be first cell of the Target range)
3. Select the WHITE font color.
4. Click OK.

You can also Copy the cell on which conditional formatting has already been applied and paste special Formatting on Target cells to apply conditional formatting.

I have revised the formula as well. Now much shorter and efficient.
For Excel2003

Please Login or Register  to view this content.

For Excel2007 or later version

Please Login or Register  to view this content.

Check the attached file.

[tpsdas]

attention Mr.Vikas
I hv used ur formula & founs it was working in the dummy file. But when used in my actual file , it was not working. there i hv observed that the LOT to extract is from validation list...
plz hv a look at the attached xls file

[Vikas_Gautam]

I have revised the formula..
the problem was due to column D which as TEXT rather than NUMBER as in the earlier example.

Please Login or Register  to view this content.

Check the attached file:-

[tpsdas]

attention Mr.Vikas
Observation:
1) Plz hv a look .with new formula (D as text) .the batch & lots are not matching. e.g 10_124 is of batch 10, but showing in batch 5

2) also col F (total spoil) why total spoil is not comimg , same formula used as total production(which is ok) . But total spoil not coming ??
plz help

[samba_ravi]

Batch No Lot No.
22 5_132
Is the above Data in Row 22, correct
i think it is 22_132, am i right

[samba_ravi]

see the attached file

[Vikas_Gautam]

Sorry,
I have corrected it now.
Have a look at Row 26 to correct the total which is not coming.
=INDEX($D$8:$D$100,SMALL(IF(OFFSET($C$8:$C$100,,,ROWS($C$8:$C$100)+1)=$C130,
IF(FREQUENCY(MATCH($D$8:$D$100,$D$8:$D$100,0),MATCH($D$8:$D$100,$D$8:$D$100,0)),ROW($D$8:$D$100)-ROW($D$8)+1)),COUNTIF($C$130:$C130,$C130)))
Check the attached file:-

[tpsdas]

Thanks Mr.Vikas & Mr. nflsales for ur quick repose.
Thanks Mr. Siva for the small formula u hv given.
I feel i can use this formula.
How ever I hv observed that if I use same lot no. for different batch it is taking only one batch . Not taking all the batches (e.g. [[batch 22 lot 5_132] and [batch 5 lot 5_132], same lot with different batch - will show only 1 batch]
Ideally same lot is not used with different batch. But i hv put it for a testing purpose to see if by mistake same is used, whether it shows any error or not.
If posible plz rectify it. else i will us it with cautions...

[samba_ravi]

Thanks Mr.Vikas & Mr. nflsales for ur quick repose.
Thanks Mr. Siva for the small formula u hv given.
I feel i can use this formula.
How ever I hv observed that if I use same lot no. for different batch it is taking only one batch . Not taking all the batches (e.g. [[batch 22 lot 5_132] and [batch 5 lot 5_132], same lot with different batch - will show only 1 batch]
Ideally same lot is not used with different batch. But i hv put it for a testing purpose to see if by mistake same is used, whether it shows any error or not.
If posible plz rectify it. else i will us it with cautions...

--
SEE ROW NO 147 it is showing the result

[Vikas_Gautam]

This is the solution which take care of all your problems.
To make it a bit short, I have used Named Range.

Please Login or Register  to view this content.

Check the attached file:-

[tpsdas]

Thanks Mr.Vikas & Mr. Nflsales for ur such a quick repose. Within ur busy schedule u both hv solved my problem. Hats off to both of u .
Both method are working as of now. Need extensive testing & put in work.
But unfortunately & frankly speaking I dont understand it fully. Wished step by step explanations if possible, so that i can learn & solve prob alone later.

[Vikas_Gautam]

Here is the Explanation:-
Before reading the explanation below, have a look at the explanation of Frequency function at the following link:- Frequency function Explained.!

Please Login or Register  to view this content.

I have shortened the ranges for the sake of simplicity

It will start by explaining the named range, I have used
ie. ConNum----> =MATCH('trial '!$D$8:$D$20,'trial '!$D$8:$D$20,0)
It converts the text into an equivalent number using row number.
It will give an array of numbers i.e. {1;1;3;4;5;6;7;8;7;6;6;3;5}
you can notice 10_122 is made equivalent to 1 and so on others.

Now I have use IF function to filter the array to return only values corresponding to say 5. like this
{FALSE;FALSE;FALSE;4;5;6;FALSE;FALSE;FALSE;6;6;FALSE;5} as first parameter of Frequency Function ie. Data Array.
And
{0;0;0;4;5;6;0;0;0;6;6;0;5} as second parameter of Frequency function as Bin_Array.

There is a reason before, using the IF function differently
That is, if you use use 0 instead of False in Bin_Array, the resultant array (as returned by Frequency function) would be more or less becomes equivalent to our Index (Array or Table parameter )

I said more or less because Frequency function add one extra count element to resultant array to include 'more than maximum bin_array_value' condition.

I have used False in Data array because Frequency function just ignores any text or boolean value if there in data array.

So resultant array by Frequency Function (including one extra element) would be {0;0;0;1;2;3;0;0;0;0;0;0;0;0}

Now I have used an extra If Function to return the row no. of values more than zero as they have fulfilled all criterias till now.
the array would be {FALSE;FALSE;FALSE;4;5;6;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

ROW($C$8:$C$20)-ROW($C$8)+1 portion will return an array of row numbers like
{1;2;3;4;5;6;7;8;9;10;11;12;13}

Now Small function will return the First small row number and Index function will return the corresponding value using row number.

and then Formula will drag the Small function will return the 2nd small and 3 rd and so on.
COUNTIF($C$130:C130,$C130) portion will reset the nth small parameter when match item switches to 5 to 10 and so on.

Use FORMULA AUDITING to understand it more with a relatively shortened ranges.

[tpsdas]

1st thanks for all the helps earlier from the Masters in this forum. Earlier I hv posted my qys and got the help.
I am not able to post a new a question. So I am posting in my earlier thread. Hope this is NOT violating the rules of forum.

MY questions are :
i hv found a xls file on inventory in net , which i want to modify & use in my inventory xls like follows:
(1) xlfn.COUNTIFS(Orders_and_Inventory!$L$14:$L$19,"<0",Orders_and_Inventory!$D$14:$D$19,">"&TODAY(),Orders_and_Inventory!$C$14:$C$19,"Sale")
(2)xlfn.SUMIFS(Orders_and_Inventory!$G$14:$G$19,Orders_and_Inventory!$D$14:$D$19,"<="&Orders_and_Inventory!$D14,Orders_and_Inventory!$F$14:$F$19,Orders_and_Inventory!$F14,Orders_and_Inventory!$C$14:$C$19,"Purchase")-_xlfn.SUMIFS(Orders_and_Inventory!$G$14:$G$19,Orders_and_Inventory!$D$14:$D$19,"<="&Orders_and_Inventory!$D14,Orders_and_Inventory!$F$14:$F$19,Orders_and_Inventory!$F14,Orders_and_Inventory!$C$14:$C$19,"Sale")
(3)INDEX(Tbl_Current_Inventory,MATCH(H5,INDEX(Tbl_Current_Inventory,,1),0),3)
(4)xlfn.IFERROR(_xlfn.SUMIFS(Orders_and_Inventory!$G$14:$G$19,Orders_and_Inventory!$D$14:$D$19,">"&TODAY(),Orders_and_Inventory!$F$14:$F$19,F4,Orders_and_Inventory!$C$14:$C$19,"SALE"),"")

My Qts are:
1) need help to convert these formulas from excel 2010 to excel 2003 so that i can use
2) Attached a sample file ( zip file)for the reference