This even possible?

**flyingby** · 10-05-2005, 02:51 PM

1-If any row in column A is empty enter "Unassigned" but only do so if Column B in that same row is not empty

2-If any row has empty fields in both column A and B switch column c of that same row to column D of the prior row.

I need to do this for 14k rows is there any formula or macro that might be able to do this?

**flyingby** · 10-05-2005, 03:11 PM

..................

T.R. Young · 10-05-2005, 05:05 PM

I don't understand.... Does my reply not make sense to you?

Question 1 - place this formula in every A1 cell:
=IF(B1>0,"Unassigned","")

Questin 2 - Place this formula in every C cell:
=IF(A5&B5="",D4,0)

That should give you the results that you are looking for.

--
"...I''m just a simple man, trying to make my way in the universe..."

"flyingby" wrote:

>
> ..................
>
>
> --
> flyingby
> ------------------------------------------------------------------------
> flyingby's Profile: http://www.excelforum.com/member.php...o&userid=27844
> View this thread: http://www.excelforum.com/showthread...hreadid=473523
>
>

**shadestreet** · 10-05-2005, 05:57 PM

Was that question aimed at me? I understand the suggestions you are giving him, they make perfect sense. I just wanted to clarify to the original poster that if his data is not sorted, and he has cells that have random blanks, other with data, he can't just paste that formula into cell A1 and drag to A14000-he would overwrite his original data. He would have to find a way to place that formula only in the cells that are already blank, easiest to me seems to do a sort, right?

**flyingby** · 10-06-2005, 04:53 PM

I cant sort it becuase when i sort then the data is no longer in order EX.
A1=ME B1=you C1=they if i sort the 3 columns then me you they will no longer be parallel to each other. It would be something like A1=Me B1=Me C1=ME.

Also the formulas are unusable becuase as it was already said if i put the formulas into the all 14k a rows then it will delete all my data in the A column same goes for the C column.

**shadestreet** · 10-05-2005, 03:39 PM

There are probably several solutions, here is my suggestion.

Part I -

Sort the entire workbook by column A ascending, then by Column B ascending.

Scan through the sorted data and you should now see all the blank "A's" together. within in this group you will all the blank B's together, and all the B's with info together as well. Since this sort has now grouped all the cells that are blank together, you can find where the data begins to have info in B but nothing in A. Type in "unnassigned" in the first row in A where there is info in B, then drag down as needed.

Part II -

This sort has also grouped the cells that are blank in A with the cells that blank in B in one matrix. Go over to column C and copy the "n" rows of data that need to be moved to D. You can now paste the info in D, and offset it any way you please (in this case you are going to offset by one row?).

That should do it.

Note - If the data was previously sorted in a specific order that you wanted to preserve, you will need to insert a temporary column in A, and then number all the rows (1 through 14,000). This will be the reference column, when you are done massaging your data you can resort the list by the new column A (the reference #'s), and then delete it when you are done.

Let me know if you want more detail.

T.R. Young · 10-05-2005, 04:05 PM

Place this in the cell that you want:

=IF(B1>0,"Unassigned")

Works if B1 is number or text.
--
"...I''m just a simple man, trying to make my way in the universe..."

"flyingby" wrote:

>
> 1-If any row in column A is empty enter "Unassigned" but only do so if
> Column B in that same row is not empty
>
> 2-If any row has empty fields in both column A and B switch column c of
> that same row to column D of the prior row.
>
> I need to do this for 14k rows is there any formula or macro that might
> be able to do this?
>
>
> --
> flyingby
> ------------------------------------------------------------------------
> flyingby's Profile: http://www.excelforum.com/member.php...o&userid=27844
> View this thread: http://www.excelforum.com/showthread...hreadid=473523
>
>

T.R. Young · 10-05-2005, 04:05 PM

Actually, you probably want this formula;

=IF(B1>0,"Unassigned","")

Where the last 2 "" will keep cell the current cell blank. Or, if you want
a text condition in the current cell, just place that text condition between
the last 2 quotes, or remove the quotes and place in a numeric value.
--
"...I''m just a simple man, trying to make my way in the universe..."

"T.R. Young" wrote:

> Place this in the cell that you want:
>
> =IF(B1>0,"Unassigned")
>
> Works if B1 is number or text.
> --
> "...I''m just a simple man, trying to make my way in the universe..."
>
>
> "flyingby" wrote:
>
> >
> > 1-If any row in column A is empty enter "Unassigned" but only do so if
> > Column B in that same row is not empty
> >
> > 2-If any row has empty fields in both column A and B switch column c of
> > that same row to column D of the prior row.
> >
> > I need to do this for 14k rows is there any formula or macro that might
> > be able to do this?
> >
> >
> > --
> > flyingby
> > ------------------------------------------------------------------------
> > flyingby's Profile: http://www.excelforum.com/member.php...o&userid=27844
> > View this thread: http://www.excelforum.com/showthread...hreadid=473523
> >
> >

T.R. Young · 10-05-2005, 04:05 PM

I forgot to answer the second part of your question....
Enter this in the "C" column cell;

=IF(A5&B5="",D4,0)

I think that this is what you are asking for.

--
"...I''m just a simple man, trying to make my way in the universe..."

"flyingby" wrote:

>
> 1-If any row in column A is empty enter "Unassigned" but only do so if
> Column B in that same row is not empty
>
> 2-If any row has empty fields in both column A and B switch column c of
> that same row to column D of the prior row.
>
> I need to do this for 14k rows is there any formula or macro that might
> be able to do this?
>
>
> --
> flyingby
> ------------------------------------------------------------------------
> flyingby's Profile: http://www.excelforum.com/member.php...o&userid=27844
> View this thread: http://www.excelforum.com/showthread...hreadid=473523
>
>

**shadestreet** · 10-05-2005, 04:13 PM

That is another good suggestion, but you would either need to sort the data first, or add a temporary column and make the formula something like "=if(C1>0,"unassigned",B1). This formula assumes you have inserted your temp column in column A to put your formula. when you run the formula on all the cells you can copy/paste-special (values only) back into B, then delete A.

I assume column A already has some blanks and some data mixed in it, thats why you need to either sort first or use the temp column approach for the formulas.... unless I am missing something.