How to find max value for every other column in a row that contains numbers and letters.

**Hans Goober** · 06-19-2014, 01:22 PM

I want to be able to have column C return the maximum value whether it be a number or a letter (In the case that there are both the number would be of higher value) for every other column in a row.

I have attached a spreadsheet to help explain the problem further.

Thanks

**martindwilson** · 06-19-2014, 02:15 PM

what column are you starting from

**Hans Goober** · 06-19-2014, 02:26 PM

Hey,

I want to start the formula for column E and finish at column W.

**martindwilson** · 06-19-2014, 02:48 PM

so you just want the revision number since all of column e is a number and that will be higher than any letter?

**Hans Goober** · 06-19-2014, 03:00 PM

Sometimes the revisions can be labeled with letters only, if there is a project with both a revision number and a letter in its row then yes I just would like for the composite column to give the largest revision number. But at the same time if the project had only letters in the row I would want the composite revision cell to give me the largest letter value. Hope that helps some.

**martindwilson** · 06-19-2014, 04:19 PM

ARRAY ENTERED
=IFERROR(IF(MAX(IF(MOD(COLUMN(E9:X9),2),E9:X9)),MAX(IF(MOD(COLUMN(E9:X9),2),E9:X9)),CHAR(MAX(IF(MOD(COLUMN(E9:X9),2),IF(E9:X9="",0,CODE(E9:X9)))))),"") seems to work

**newdoverman** · 06-19-2014, 04:30 PM

I made the assumption that all dates would have a value greater than 1000 and all revisions numbers would be less than 1000. This also includes the first revision in column D.

Array enter this formula in C9 and copy down.

Formula:

Please Login or Register  to view this content.

**martindwilson** · 06-19-2014, 04:55 PM

@newdoverman so what happens if the revisions are just A B C? that gives 0

Sometimes the revisions can be labeled with letters only

sic

**newdoverman** · 06-19-2014, 05:00 PM

@ martindwilson......true. The possible mix of characters with digits seems impossible.

I tried your formula before attempting mine and I get the same results

**martindwilson** · 06-19-2014, 05:04 PM

well it gave me an idea array entered

Formula:

Please Login or Register  to view this content.

**newdoverman** · 06-19-2014, 05:32 PM

@martindwilson

How about starting in column G so that the first revision is ignored and only further revisions are considered. This will show a blank if there are no further revisions and show the actual largest character if the revision is alphabetic and the largest digit if numeric.

This is your formula with suggestions above.

Formula:

Please Login or Register  to view this content.

	B	C	D	E	F	G	H	I
7	SHEET NAME	Composite		Original	6/15/14	Rec Date	#########	Rec Date
8		Rev	Date	Rev	Date	Rev	Date	Rev
9	Floor Framing	4	08/15/14	1	6/2/14	2	#########	4
10	Overall Plan Level 06	10	06/13/14	1	6/2/14	9		10
11	Floor Framing Plan 1 Level		06/02/14	1	6/2/14
12	Floor Framing Plan 2 Level	2	07/04/14	1	6/2/14	A	#########	B
13	Floor Framing Plan 5 Level	2	06/29/14	1	6/2/14			2
14	Floor Framing Plan 9 Level	X	06/02/14	1	6/2/14	X		N
15	Floor Framing Plan 10 Level		06/02/14	1	6/2/14
16	Floor Framing Plan	2	06/29/14	2	6/2/14			2

**martindwilson** · 06-19-2014, 05:56 PM

yes but op said that sometimes there are just letters so the original may be a letter and may be the only one

**newdoverman** · 06-19-2014, 06:11 PM

@martindwilson

I was mislead by the lack of alpha characters in column E of the example.

I hope that there are no more variations in what the codes could be.....A2, CC ...etc.

Your solution indeed does work.

**Hans Goober** · 06-20-2014, 01:52 PM

Thanks you guys for the help. Saved me a ton of time at work.

How to find max value for every other column in a row that contains numbers and letters.

LinkBack

Thread Tools

Rate This Thread

Display

How to find max value for every other column in a row that contains numbers and letters.

Re: How to find max value for every other column in a row that contains numbers and letter

Re: How to find max value for every other column in a row that contains numbers and letter

Re: How to find max value for every other column in a row that contains numbers and letter

Re: How to find max value for every other column in a row that contains numbers and letter

Re: How to find max value for every other column in a row that contains numbers and letter

Re: How to find max value for every other column in a row that contains numbers and letter

Re: How to find max value for every other column in a row that contains numbers and letter

Re: How to find max value for every other column in a row that contains numbers and letter

Re: How to find max value for every other column in a row that contains numbers and letter

Re: How to find max value for every other column in a row that contains numbers and letter

Re: How to find max value for every other column in a row that contains numbers and letter

Re: How to find max value for every other column in a row that contains numbers and letter

Re: How to find max value for every other column in a row that contains numbers and letter

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Find and Delete all letters & leave numbers

[SOLVED] Find missing entries from a list of sequential numbers and letters in a column?

[SOLVED] Separating a column by numbers vs. letters when numbers are at the end of the cells?

Find all possible combinations of letters and numbers

Find missing values in logical sequence of letters+numbers

Bookmarks

Bookmarks

Posting Permissions