Transfer data from one column to another based on condition

[aTmb405]

I am looking for a way to transfer the data using a formula to accomplish this:

[TABLE="width: 195"]
<!--StartFragment--> <colgroup><col width="65" span="3" style="width:65pt"> </colgroup><tbody>[TR]
[TD="class: xl64, width: 65"]A[/TD]
[TD="class: xl64, width: 65"]B[/TD]
[TD="class: xl64, width: 65"]C[/TD]
[/TR]
[TR]
[TD="class: xl64"]First[/TD]
[TD="class: xl64"]x[/TD]
[TD="class: xl64"]First[/TD]
[/TR]
[TR]
[TD="class: xl64"]Second[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"]Third[/TD]
[/TR]
[TR]
[TD="class: xl64"]Third[/TD]
[TD="class: xl64"]x[/TD]
[TD="class: xl64"]Fourth[/TD]
[/TR]
[TR]
[TD="class: xl64"]Fourth[/TD]
[TD="class: xl64"]x[/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl64"]Fifth[/TD]
[TD="class: xl64"] [/TD]
[TD="class: xl64"] [/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]


Data from 'A' is transferred to 'C' based off of cell content in 'B'.

Is this possible? I've tried different IF functions, ROW functions, etc, but nothing has been able to accomplish this.

 

[Eric W]

Something like this?

Excel 2010

	A	B	C
Column A	Column B	Column C
First	x	First
Second	Third
Third	x	Fourth
Fourth	x
Fifth

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet17​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]{=IF(C1="","",IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$10="x",ROW($B$2:$B$10)),ROWS($C$2:$C2))),""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Enter the C2 formula with Control-Shift-Enter. Then copy that cell and paste it down the column as far as needed.

Let me know how this works.

 

[aTmb405]

Thank you! That worked perfectly. If it is not too much trouble, would you mind giving me a short explanation for why this works? Also, will this work with a larger table?

 

[aTmb405]

For clarification, I am using Excel for Mac 2011

 

[aTmb405]

How would the formula change if the data in column A what in Sheet 1 and the data in columns B and C were in Sheet 2? Sorry for so many questions!

 

[Eric W]

I appreciate it when someone asks questions. I think it's better if they gain an understanding, so that they can do more on their own later. Here's a brief rundown of how it works:

First of all, an array function (the Control-Shift-Enter part) tells a function to handle a range of values instead of 1 value. For example, if you have a formula

=A1+1

where A1=1, then the result will be 2. If you turn it into an array formula by putting a range instead of a single cell and confirm with CSE, like this:

=A1:A3+1

where A1=1, A2=2, A3=3, then the result will be {2,3,4} in an array. It's possible to show the whole array on a sheet by entering the formula in a range of cells, but for this formula we just use the array internally.

Back to the formula. This part:

IF($B$2:$B$10="x",ROW($B$2:$B$10))

translates to

IF(B2="x",ROW(B2)) which evaluates to 2 in our example
IF(B3="x",ROW(B3)) evaluates to 3
IF(B4="x",ROW(B4)) evaluates to FALSE
IF(B5="x",ROW(B5)) evaluates to 5
etc.

so the array from the IF ends up as

{2,3,FALSE,5,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}

now we need to extract the row numbers from the array. The ROWS($C$2:$C2) part now comes into play. The ROWS function tells us how many rows are in the range. So ROWS($C$2:$C2) will equal 1. But as we copy that piece down the column, since the second 2 does not have a $ on it, it will act as a relative reference and change to ROWS($C$2:$C3), which will evaluate to 2. So that piece just converts to 1, 2, 3, 4, etc. as we go down the column.

So now we have

=SMALL({2,3,FALSE,5,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}, 1)

where the 1 increments as we go down the column. So in row 2 we get row 2 from the array, in row 3 we get 3 (second smallest) from the array, in row 4 we get 5 from the array (third smallest), and for all other rows we get an error (SMALL ignores non-numeric values).

Then finally we take the 2, 3, 5 from that step and put it in the INDEX function to get the item from column A. The IFERROR piece is used to ignore the potential error from the last step. And the IF(C1="","" part at the beginning is to tell the function not to calculate at all if the row above it is empty. This is to improve the overall performance of the sheet by not doing lots of array formulas that aren't necessary.

And as to your questions, yes it will work on larger ranges. Just change the ranges in the formula to match your sheet. And it will work on separate sheets, just include the sheet reference, like so:

=IF(C1="","",IFERROR(INDEX(Sheet1!A:A,SMALL(IF(Sheet2!$B$2:$B$10="x",ROW($B$2:$B$10)),ROWS($C$2:$C2))),""))

Hope that helps!

 

[aTmb405]

Thank you for the explanation! That is very helpful.

Tomorrow, I will try the formula on data in separate sheets and let you know how it goes.

 

[aTmb405]

The formula worked perfectly across sheets. Thank you so much for all your help!

 

[Eric W]

Happy to help!