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

cosima

New Member
Joined
Apr 16, 2015
Messages
9
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 :


TypeType1Type1Type2Type3Type1Type1Type2
InvoiceId200002000140005000020002200034001
ClientId333444556666777888999
Amount1220333666555888666333

<tbody>
</tbody>

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 ?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

QU4487

Board Regular
Joined
Jul 3, 2013
Messages
178
Try this, is you want to return the Max value for a particular "Type";

Excel 2012
ABCDEFGH
1TypeType1Type1Type2Type3Type1Type1Type2
2InvoiceId200002000140005000020002200034001
3ClientId333444556666777888999
4Amount1220333666555888666333
5
6
7Max Invoice ID
8Type120003
9Type24001
10Type350000

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
B8{=MAX(IF($B$1:$H$1=A8,$B$2:$H$2))}
B9{=MAX(IF($B$1:$H$1=A9,$B$2:$H$2))}
B10{=MAX(IF($B$1:$H$1=A10,$B$2:$H$2))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Hope this is what you're looking for.

Q
 

cosima

New Member
Joined
Apr 16, 2015
Messages
9
Try this, is you want to return the Max value for a particular "Type";

Excel 2012
ABCDEFGH
1TypeType1Type1Type2Type3Type1Type1Type2
2InvoiceId200002000140005000020002200034001
3ClientId333444556666777888999
4Amount1220333666555888666333
5
6
7Max Invoice ID
8Type120003
9Type24001
10Type350000

<tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
B8{=MAX(IF($B$1:$H$1=A8,$B$2:$H$2))}
B9{=MAX(IF($B$1:$H$1=A9,$B$2:$H$2))}
B10{=MAX(IF($B$1:$H$1=A10,$B$2:$H$2))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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

Board Regular
Joined
Jul 3, 2013
Messages
178
Where do you need the formula to be? You're getting a circular reference because the formula is using row 2.
 

cosima

New Member
Joined
Apr 16, 2015
Messages
9

ADVERTISEMENT

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

Board Regular
Joined
Jul 3, 2013
Messages
178
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

New Member
Joined
Apr 16, 2015
Messages
9

ADVERTISEMENT

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

New Member
Joined
Apr 16, 2015
Messages
9
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

New Member
Joined
Apr 16, 2015
Messages
9
Invoice TypeType1Type1Type2Type3Type1Type1Type2
InvoiceId2000020001#VERDI!#VERDI!#VERDI!#VERDI!#VERDI!
ClientId333444556666777888999
Amount1220333666555888666333
Table TypesMin IDMax ID Translations :
Type12000029999 #VERDI = #VALUE
Type240004999 MAKSA =MAX
Type35000059999HVIS =IF
; =,
Formula : MAKSA(HVIS($B$1:C$1=D1;$B$2:C$2+1;HVIS(D1="Type1";20000;HVIS(D1="Type2";4000;HVIS(D1="Type3";50000)))))
translated: MAX(IF($B$1:C$1=D1;$B$2:C$2+1,HVIS(D1="Type1",20000,HVIS(D1="Type2",4000;HVIS(D1="Type3",50000)))))
Error : In d2 warning is given (!) --> A value used in the formula has wrong datatype / In dropdown warning : Wrong value
Debug : When step/by step in drop down warning :
Evaluate --> #VALUE=Type2

Datatype:

D1 and D2 have datatype Standard (Tested with text but same result)

<colgroup><col><col span="7"></colgroup><tbody>
</tbody>


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 !!!
 

Forum statistics

Threads
1,136,970
Messages
5,678,895
Members
419,788
Latest member
Mukund2903

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top