Count amount and quality of order set in row.

[Frigide]

How to make a table from a long sheet with orders and add how much every order is and what the quality is of that order.

[Frigide]

I like to know if this is not possible at all. Someone?

[jindon]

Try

Please Login or Register  to view this content.

[maniacb]

You can also try, your orders will show up in reverse wit this method

Please Login or Register  to view this content.

[Bo_Ry]

Please try

formula at
B2
=IFERROR(INDEX(Sorce!$D$3:$AK$3,AGGREGATE(15,6,COLUMN(Sorce!$D$3:$AK$3)/(Sorce!$D$3:$AK$3>0),ROWS(B$3:B3))-COLUMN(Sorce!$C$3)),"")

D2
=IF(B3="","",SUMPRODUCT(--(LOOKUP(COLUMN(Sorce!$D$3:$AK$3),COLUMN(Sorce!$D$3:$AK$3)/(Sorce!$D$3:$AK$3>0),Sorce!$D$3:$AK$3)=B3),Sorce!$D$4:$AK$4))

E2

Please Login or Register  to view this content.

or VBA

Please Login or Register  to view this content.

[Frigide]

This is great, you guys are awesome.

How can i change the formula to give the lowest and highest value.

[Bo_Ry]

The Highest Amount , for the lowest change 14 to 15

=IF(B3="","",AGGREGATE(14,6,Sorce!$D$4:$AK$4/(LOOKUP(COLUMN(Sorce!$D$3:$AK$3),COLUMN(Sorce!$D$3:$AK$3)/(Sorce!$D$3:$AK$3>0),Sorce!$D$3:$AK$3)=B3),1))

Max %
=IF(B3="","",AGGREGATE(14,6,Sorce!$D$6:$AK$6/(LOOKUP(COLUMN(Sorce!$D$3:$AK$3),COLUMN(Sorce!$D$3:$AK$3)/(Sorce!$D$3:$AK$3>0),Sorce!$D$3:$AK$3)=B3)/(Sorce!$D$6:$AK$6<>""),1))

[Frigide]

Works great thanks. I'm building a view nice sheets and extending is with the answers you give me.
When ad dates in row 2, what is the formule to get the date when a order was started.

[Frigide]

Very happy with the result. A view thinks i like to at.

1) The formulas in the red cell don.t work because the need the outcome of certain formulas. Why?
2) I like the date of the beginning of a order.
3) calculating the page takes a very long time. Is there a way to make it quicker?

i hope you can help (again!).

[Bo_Ry]

1. Use AGGREGATE with option 5 or 7 to ignore only the Hidden row
=AGGREGATE(1,7,Tabel1[DSC max])

2. B9
=IF(D9="","",INDEX(Sorce!$D$2:$BJ$2,MATCH(D9,Sorce!$D$3:$BJ$3,)))

3. It won't get any faster with the layout on sheet Sorce.

Change layout to D12:Hxx then you can apply PIVOT which is a lot faster than array formula.

[amarjeet.it]

I think this would work as per your requirements

Please Login or Register  to view this content.

[Frigide]

The AGGREGATE is wroking, but when i use a filter the outcome is of all the values and not only form the filtert values.

I want the outcome from the filtert values only.

My bad, it does work. Auto cal was off because it take to lang.


I can't change the layout because the file i gonna use it on has 3 years of data all ready and is growing.

Is there a way to turn off auto cal on a specific sheet??

[Frigide]

This is what i get so far. The VBA option work well. But i don't understand the code so i cant at things. I want a easy way to ad things.

This is what i have so far.
1) wil it help to speed things up if i ad a row with "current order" row 4?
2) Is there a way to make a chart that only ads date filter out. The list will be a very long list. Filters will be needed to make a chart of the data needed in the chart.
3) is there a way to sort the data. Now the empty cell are messing this up.
4) Is it posible to recalculate the formulas is the formule sheet row 4:6 when a filter is set or a button is pusht, but recalculate all the formules in the rest of the formula sheet?
5) When i add a row and give several number like this: 19/2568.1 19/2685.4 17/3265.3 How can i search in every order the highest last number. in this case 4.
6) Is there a way to set the date filter between 2 dates added in 2 cells?

i know, these are a lot o questions. But i learn a lot and google isn't always helpful.

Edit a new file!

[Frigide]

I hope someone can help.

In a factory we have to line's. For a little track & trace i made a new sheet in a 2 year old excel file that holds a lot off data.
The data is set as in sheet Sorce, I like to have a table with all the orders from two line's.

I made (with help from all of you) a table with almost all the data from one line. I only need to filter out the oldest LF. The number of a filter is: **/****.* The last * tells the number off times the filter is used.

My most urgent questions are:
1) How can I merge the two line in one table. I prefer a formule that is looking at both line.
2) How can I search for the oldest filter used in a order. (The highest last number)

I added 2 file's. They are the same, one with Macros's.

Again, I hope someone can help

[Frigide]

1. Use AGGREGATE with option 5 or 7 to ignore only the Hidden row
=AGGREGATE(1,7,Tabel1[DSC max])

2. B9
=IF(D9="","",INDEX(Sorce!$D$2:$BJ$2,MATCH(D9,Sorce!$D$3:$BJ$3,)))

3. It won't get any faster with the layout on sheet Sorce.

Change layout to D12:Hxx then you can apply PIVOT which is a lot faster than array formula.

Dear Bo_Ry,

In the topic "Count amount and quality of order set in row" you help me allot. There is one last ting i like to at in my sheet.

In every order we use several filters. Each time we use a filter we at up a number so we know how many time a filter is used. Can you help me create a formula to find the highest number in every order?

A filter number will look like this: 18-2026/5.2 or 19-2386/2.8 or 16-2322/2.11

The number after the dot is the number of times the filter is used. In this case 2, 8 and 11.

Is it possible to create a formula that will find the highest number after the dot in a order?

Great regards