Extract data across multiple columns?

**Fao** · 09-23-2010, 11:20 AM

Hi, I was wondering if anyone could help me figure out a way to extract the largest (actually newest) date captured by our systems across multiple columns.

Example: (This is what I need it to do)
| Column A | | Column B | | Column B | | Column D | | Column E |

|Last Check -------------------------------------------------> | | Max/Large|

| #N/A | | #N/A | | 9/22/10 | | 1/0/00 | | *9/22/10 |* (returned value)
| 9/21/10 | | #N/A | | #N/A | | 1/0/00 | | *9/21/10 |* (returned value)

The problem occurs when I try to compare the #N/A with the "real" values, and the value returned is #N/A

.
But I need to add the #N/A because these are not the only values in that specific column.

I hope this is clear because I could really use the help. Thanks.Edit: Forgot the attachment. Again...Thanks.

**MarvinP** · 09-23-2010, 11:44 AM

Try:

=IF(ISERROR(D3),F3,MAX(D3,F3))

and copy it down.

**Fao** · 09-23-2010, 12:06 PM

Hey, Thanks alot for the help and quick repsonse. But I still run into the #N/A problem. If #N/A shows up in another column and not just the ones specified in the formula. I need a way to compare the data across columns and extract the newest/latest date no matter what information is in each respective column.

**MarvinP** · 09-23-2010, 03:12 PM

OK then, try this:

=MAX(IFERROR($B3,0),IFERROR($C3,0),IFERROR($D3,0),IFERROR($E3,0),IFERROR($F3,0))

**Fao** · 09-23-2010, 05:26 PM

OMG!!! This is exactly what I was looking for. Thank you so much! I work with Altiris/Symantec and this will help me shift through this Data mining project I'm doing that much easier. If I can help you with something let me know. Thanks again!

**Fao** · 09-24-2010, 09:48 AM

Hey, thanks again for the help with the data/date formula but I have another question. Is the inverse of this formula possible?

=MAX(IFERROR($B3,0),IFERROR($C3,0),IFERROR($D3,0),IFERROR($E3,0),IFERROR($F3,0))

where MAX would be MIN, and I extract the lowest/oldest records across columns?

**MarvinP** · 09-24-2010, 10:49 AM

Hi Fao,
For a Min of your values try:

=MIN(IFERROR($B3,TODAY()),IFERROR($C3,TODAY()),IFERROR($D3,TODAY()),IFERROR($E3,TODAY()),IFERROR($F3,TODAY()))

This will work unless the earlies date is in the future.

**Fao** · 09-24-2010, 11:47 AM

Thank You.Perfect. Formula works great. I really, really, appreciate the help.

Extract data across multiple columns?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Extract data across multiple columns?

Re: Extract data across multiple columns?

Re: Extract data across multiple columns?

Re: Extract data across multiple columns?

Re: Extract data across multiple columns?

Re: Extract data across multiple columns?

Re: Extract data across multiple columns?

Re: Extract data across multiple columns?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions