How to separate a single column of text cells and date cells

[MelindaCapri]

Hi,

Thanks in advance for your help!!!

Example:

Column A Row 1 : MelindaCapri
Column A Row 2 : 3/17/12
Column A Row 3 : 5/16/08
Column A Row 4 : StaceyRae
Column A Row 5 : 6/18/13
Column A Row 6 : 9/25/06

How do I separate the dates into Column B ?

[cnodnarb]

Interesting problem. (First post to this forum, forgive me I don't know how to make codeblocks and whatnot just yet)

The first thing I would try without delving into a VBA solution is as follows

In cell B1:
=IF(ISNUMBER(A1),A1,"")
This should show up blank, because the name in A1 is not numeric.
Copy cell B1 all the way down without locking cells.
If there is a date in column A it will now show up in column B in number format, you'll have to reformat it as date.

In cell C1:
=IF(ISNUMBER(A1),"",A1)
If it is not numeric it should show up. Copy this all the way down.

In Column B you now have all the dates.
In Column C you now have all the names.

[MelindaCapri]

Thank you cnodnarb that totally worked now I might need to create a new thread for this part; and I will if needed ....

So I have names in Column A
dates in Column B and
test scores on the dates of the names in Coulmn C.

Example :
Column A Row 1 : Melinda Capri
Column B Row 2 : 3/17/12
Column B Row 3 : 5/16/08
Column C Row 2 : 98
Column C Row 3 : 87
Column D Row 2 : 74
Column D Row 3 : 91
Column A Row 4 : StaceyRae
Column B Row 5 : 6/18/13
Column B Row 6 : 9/25/06



Ultimately, I want to find the total of the max score of Row 2 + max score Row 3 for Melinda Capri; and then again for Stacey Rae (who may have 1 - any number of dates) and so on ..
I know the data is not in an ideal format; it came from a pivot table with names AND dates in rows and test scores in columns.

[cnodnarb]

Is your goal ultimate to derive the average of max scores for each person or is it to accumulate the total sum?

[cnodnarb]

Are there always only two test scores per date?

[MelindaCapri]

Goal is to accumulate total sum of max test scores. There are maximum 3 scores per date, but any number of dates. (Max number of dates apx. 10)

[cnodnarb]

Try this on please.

[cnodnarb]

Solution assumed max of two test scores!

[cnodnarb]

Spot for third score added.

[MelindaCapri]

Okay this is SORTA working ... but where there are more than 3 dates I have to change the formula

=IF(ISNUMBER(Q4),A3,Q4) =IF(ISNUMBER(Q5),A4,Q5) I think ...

I am attaching an example of my spreadsheet, hope this helps you help me

[cnodnarb]

This required entering formula's as arrays. Tutorial here: http://blog.contextures.com/archives...x-if-in-excel/

Solution attached

[MelindaCapri]

I removed some of the data and added some others so you can see the what I am trying to accomplish a little better I hope!
attachment "Complete Example"

[MelindaCapri]

Oh i am sorry wait ... I need to try you most recent solution thank you!

[cnodnarb]

Well, you're gonna have to choose a standard way for the data to be presented / pulled before anybody can help you Line item data is different that pivot data, and data pulled from pivot data differs from that, and now you've got data pulled from pulled pivot data.

Pick one.

[cnodnarb]

If you go with my last posted solution, there is an error where if the previous user took the test on the same date as the current user then it returns 0. About line 181 I think showed that.

[MelindaCapri]

Yes, i see that I attached the wrong file; my sincere apologies!!! Don't give up on me quite yet please! The "Complete Example" is a beginning to end of the table I mention in initial post. Numbers have replaced the names, and I included the pivot table also. I am one behind in replies - I hope this catches us up a bit.

[cnodnarb]

I have to go ;(

This is as far as I can got, it's solved, but not prettified.

[MelindaCapri]

Thank you for trying and I apologize for incorrect attachment. Maybe I posted in wrong area also!

[cnodnarb]

Nah, you did good! I just ran out of time is all

Can you work with my solution? (Is it good enough to implement?) If no be sure to repost the problem!