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

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


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

Place this in the cell that you want:

=IF(B10,"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

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.


--
shadestreet
------------------------------------------------------------------------
shadestreet's Profile: http://www.excelforum.com/member.php...fo&userid=7092
View this thread: http://www.excelforum.com/showthread...hreadid=473523

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

Actually, you probably want this formula;

=IF(B10,"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(B10,"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

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(C10,"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.


--
shadestreet
------------------------------------------------------------------------
shadestreet's Profile: http://www.excelforum.com/member.php...fo&userid=7092
View this thread: http://www.excelforum.com/showthread...hreadid=473523

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

Question 1 - place this formula in every A1 cell:
=IF(B10,"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

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?


--
shadestreet
------------------------------------------------------------------------
shadestreet's Profile: http://www.excelforum.com/member.php...fo&userid=7092
View this thread: http://www.excelforum.com/showthread...hreadid=473523

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.


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