Get max value on unsorted row with 3 different number groups.

[cosima]

The table in Excel 2010 is used to regeister all details for 3 different types of invoices each
in a column and each ID is a sequence within each group. I want to use a formula to solve this.


The groups of invoice ID's :
Type1 range 20000-30000
Type2 range 4000-10000
Type3 range 50000-60000


Excample row should look like this :


[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Type1[/TD]
[TD]Type1[/TD]
[TD]Type2[/TD]
[TD]Type3[/TD]
[TD]Type1[/TD]
[TD]Type1[/TD]
[TD]Type2[/TD]
[/TR]
[TR]
[TD]InvoiceId[/TD]
[TD]20000[/TD]
[TD]20001[/TD]
[TD]4000[/TD]
[TD]50000[/TD]
[TD]20002[/TD]
[TD]20003[/TD]
[TD]4001[/TD]
[/TR]
[TR]
[TD]ClientId[/TD]
[TD]333[/TD]
[TD]444[/TD]
[TD]55[/TD]
[TD]6666[/TD]
[TD]777[/TD]
[TD]888[/TD]
[TD]999[/TD]
[/TR]
[TR]
[TD]Amount[/TD]
[TD]1220[/TD]
[TD]333[/TD]
[TD]666[/TD]
[TD]555[/TD]
[TD]888[/TD]
[TD]666[/TD]
[TD]333[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have tried using HLOOKUP :
HLOOKUP ("Max-value range";$B$2:Z$3;1;TRUE)+1 to get max value from row 1. THe result is not
as expected so can anyone help me ?

 

[QU4487]

Try this, is you want to return the Max value for a particular "Type";

Excel 2012

	A	B	C	D	E	F	G	H
Type	Type1	Type1	Type2	Type3	Type1	Type1	Type2
InvoiceId
ClientId
Amount
Max Invoice ID
Type1
Type2
Type3

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

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

[TD="align: right"]20000[/TD]
[TD="align: right"]20001[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]20002[/TD]
[TD="align: right"]20003[/TD]
[TD="align: right"]4001[/TD]

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

[TD="align: right"]333[/TD]
[TD="align: right"]444[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]6666[/TD]
[TD="align: right"]777[/TD]
[TD="align: right"]888[/TD]
[TD="align: right"]999[/TD]

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

[TD="align: right"]1220[/TD]
[TD="align: right"]333[/TD]
[TD="align: right"]666[/TD]
[TD="align: right"]555[/TD]
[TD="align: right"]888[/TD]
[TD="align: right"]666[/TD]
[TD="align: right"]333[/TD]

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

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

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

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

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

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

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

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

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

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

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B8[/TH]
[TD="align: left"]{=MAX(IF($B$1:$H$1=A8,$B$2:$H$2))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B9[/TH]
[TD="align: left"]{=MAX(IF($B$1:$H$1=A9,$B$2:$H$2))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B10[/TH]
[TD="align: left"]{=MAX(IF($B$1:$H$1=A10,$B$2:$H$2))}[/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]

Hope this is what you're looking for.

Q

 

[cosima]

Try this, is you want to return the Max value for a particular "Type";

Excel 2012

	A	B	C	D	E	F	G	H
Type	Type1	Type1	Type2	Type3	Type1	Type1	Type2
InvoiceId
ClientId
Amount
Max Invoice ID
Type1
Type2
Type3

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

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

[TD="align: right"]20000[/TD]
[TD="align: right"]20001[/TD]
[TD="align: right"]4000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]20002[/TD]
[TD="align: right"]20003[/TD]
[TD="align: right"]4001[/TD]

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

[TD="align: right"]333[/TD]
[TD="align: right"]444[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]6666[/TD]
[TD="align: right"]777[/TD]
[TD="align: right"]888[/TD]
[TD="align: right"]999[/TD]

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

[TD="align: right"]1220[/TD]
[TD="align: right"]333[/TD]
[TD="align: right"]666[/TD]
[TD="align: right"]555[/TD]
[TD="align: right"]888[/TD]
[TD="align: right"]666[/TD]
[TD="align: right"]333[/TD]

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

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

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

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

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

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

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

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

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

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

</tbody>

Sheet1​

[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="bgcolor: #DAE7F5"]B8[/TH]
[TD="align: left"]{=MAX(IF($B$1:$H$1=A8,$B$2:$H$2))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B9[/TH]
[TD="align: left"]{=MAX(IF($B$1:$H$1=A9,$B$2:$H$2))}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B10[/TH]
[TD="align: left"]{=MAX(IF($B$1:$H$1=A10,$B$2:$H$2))}[/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]

Hope this is what you're looking for.

Q

Hi and thanks for helping but when I put the formula on row 2 (IncoiceID) I get circle ref and that is also a problem I got.

Do you have any proposal to solve it ?

Best regards!

 

[QU4487]

Where do you need the formula to be? You're getting a circular reference because the formula is using row 2.

 

[cosima]

I need to register each invoice per column by selecting a dropdown with Type1-3 and then get it's next available invoiceID on row 2 for InvoiceID's.

 

[QU4487]

I think I get what you're attempting to do. Maybe. You want to keep adding columns to the right of Column A. In Row 1 you then have a drop down of which type it is, and in row 2 you then want your formula. This formula will then be one number higher than the previous highest for that type. Is this right?

The below formula will do this. But it isn't perfect. As you insert the column and paste the formula in you need to make sure that column C stays as column C.

=MAX(IF($C$1:$I$1=B1,$C$2:$I$2))+1

My only other recommendation is to find somebody who can write a macro which does this. Macro's are most definitely not my specialty.

Q

 

[cosima]

Hi and thanks for your support but I can't make it work properly. I'm pasting the formula into d2, e2 and onto the rest of the row. Result is not OK ?
Perhaps I have done it wrong ? Also I try to stick with using formulas not makros and Vb.

 

[cosima]

Hi and thanks for your support but I can't make it work properly. I'm pasting the formula into d2, e2 and onto the rest of the row. Result is not OK ?
Perhaps I have done it wrong ? Also I try to stick with using formulas not makros and Vb.

I forgot to feedback that your understanding of how it should work is correct. The other question is did you test your formula in row 2 in the sample table ?

Lokk forward to you and others feedback !

 

[cosima]

[TABLE="width: 824"]
<colgroup><col><col span="7"></colgroup><tbody>[TR]
[TD]Invoice Type[/TD]
[TD]Type1[/TD]
[TD]Type1[/TD]
[TD]Type2[/TD]
[TD]Type3[/TD]
[TD]Type1[/TD]
[TD]Type1[/TD]
[TD]Type2[/TD]
[/TR]
[TR]
[TD]InvoiceId[/TD]
[TD]20000[/TD]
[TD]20001[/TD]
[TD]#VERDI![/TD]
[TD]#VERDI![/TD]
[TD]#VERDI![/TD]
[TD]#VERDI![/TD]
[TD]#VERDI![/TD]
[/TR]
[TR]
[TD]ClientId[/TD]
[TD]333[/TD]
[TD]444[/TD]
[TD]55[/TD]
[TD]6666[/TD]
[TD]777[/TD]
[TD]888[/TD]
[TD]999[/TD]
[/TR]
[TR]
[TD]Amount[/TD]
[TD]1220[/TD]
[TD]333[/TD]
[TD]666[/TD]
[TD]555[/TD]
[TD]888[/TD]
[TD]666[/TD]
[TD]333[/TD]
[/TR]
[TR]
[TD]↓[/TD]
[TD]↓[/TD]
[TD]↓[/TD]
[TD]↓[/TD]
[TD]↓[/TD]
[TD]↓[/TD]
[TD]↓[/TD]
[TD]↓[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Table Types[/TD]
[TD]Min ID[/TD]
[TD]Max ID[/TD]
[TD] [/TD]
[TD]Translations :[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Type1[/TD]
[TD]20000[/TD]
[TD]29999[/TD]
[TD] [/TD]
[TD]#VERDI = [/TD]
[TD]#VALUE[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Type2[/TD]
[TD]4000[/TD]
[TD]4999[/TD]
[TD] [/TD]
[TD]MAKSA =[/TD]
[TD]MAX[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Type3[/TD]
[TD]50000[/TD]
[TD]59999[/TD]
[TD][/TD]
[TD]HVIS =[/TD]
[TD]IF[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]; =[/TD]
[TD],[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Formula :[/TD]
[TD="colspan: 7"] MAKSA(HVIS($B$1:C$1=D1;$B$2:C$2+1;HVIS(D1="Type1";20000;HVIS(D1="Type2";4000;HVIS(D1="Type3";50000)))))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]translated:[/TD]
[TD="colspan: 7"] MAX(IF($B$1:C$1=D1;$B$2:C$2+1,HVIS(D1="Type1",20000,HVIS(D1="Type2",4000;HVIS(D1="Type3",50000)))))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Error :[/TD]
[TD="colspan: 7"] In d2 warning is given (!) --> A value used in the formula has wrong datatype / In dropdown warning : Wrong value[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Debug :[/TD]
[TD="colspan: 3"] When step/by step in drop down warning : [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Evaluate --> [/TD]
[TD]#VALUE=Type2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
Datatype: [/TD]
[TD="colspan: 5"]
D1 and D2 have datatype Standard (Tested with text but same result)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


COMMENT : I have now tested the formula but it ends with #VERDI = #VALUE. Inside the formula it states "Formula result" correctly but shows #VALUE ???

I will be very glad to receive any help and feedback !!!