Formula for copying row if certain requirements are met ( had this working )

[Krob189]

Hi there,

I am working with some telecom data and had a formula working perfectly last month, I lost my laptop ( drive crashed ) and cannot for the life of me get this formula to work the way it used to.

Capture.PNG

Here is what I am working with, I have 3 columns, Column A is a Business name, B is an indent column 0 is the main listings and 1 is going to be an indented listing, C is going to be the Indented info.

What I need to do is create a working formula that will copy Column A data down if there is a 1 in column B.

Example,

I need my screenshot to read

Column A | Column B | Column C
------------------------------------------------
1.Header A | Header B | Header C
2.Alberstons Sav-On |0 |Alberstons Sav-On
3.Alberstons Sav-On |1 |Food Center
4.Alberstons Sav-On |1 |
5.Alberstons Sav-On |1 |Sav-On Pharmacy
6.Alberstons Sav-On |1 |


What I was using, ( which I could've sworn was working
=IF(B2=0,A2,A1)

I am not concerned about Column C because this data is usually already in place it's just the Column A

If anyone can help me out with this I'd be really greatful as this takes me forever to do manually as many rows are not indented and I am working with files upwards of 26,000 lines

[FDibbins]

Hi, welcome to the forum

There is no formula that will "copy" info down, formulas only work on the cell they reside in.

So, with that said, in A3 you could put =A2, and copy it down as needed

hmm OR could could use this ALL the way down, and just type over the formula if you need to enter a new name...

A3=if(B3=1,A2,"")

[Krob189]

Hi, welcome to the forum

There is no formula that will "copy" info down, formulas only work on the cell they reside in.

So, with that said, in A3 you could put =A2, and copy it down as needed

hmm OR could could use this ALL the way down, and just type over the formula if you need to enter a new name...

A3=if(B3=1,A2,"")

Hi FDibbins,

Thank you for the welcoming,

So I did have this working, Basically what I had originally done was plug my formula into a new column, And when I drug the formula down through my spreadsheet it would essentially take whatever was above an indented row.

Example

A1|Sav-On | 0
A2|Pharmacy| 1
A3|Store Hours| 1

The formula would copy Sav-On into A2 row which would then make A3 grab the words Sav-On because it was directly above it. Sorry if this is confusing it is hard to explain.

*** Edit***

Found some emails from a friend who was originally helping me with this a few months back

it should be =IF(B2=0,C2,A1)
So, if B2 is 0, then use C2, otherwise use A1
IF there are multiple indents, then C2 will still be non-zero (IE: 2, 3, 4, 5 etc)

I know he used open office's Calc, but I am not having luck their either.


***** Second Edit ( Sorry ) ****

I got it figured out! if anyone else needs to use this formula for whatever reason..
The False value needs to be whats in the new column!

=IF(C2=0,A2,O1)

[AliGW]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[FDibbins]

Hi FDibbins,.....

***** Second Edit ( Sorry ) ****

I got it figured out! if anyone else needs to use this formula for whatever reason..
The False value needs to be whats in the new column!

=IF(C2=0,A2,O1)

I used row 2 and 3 because you had headings in row 1.

Also, where does column O come from now?

[AliGW]

Looks like the second edit was posted at the same time as my request for a workbook!

It's better if you create new posts so that the thread maintains a natural flow.

[FDibbins]

Ali, I agree entirely.

Krob, please just add new posts, rather than updating older posts with new info. As Ali poited out, if keeps the flow and sequence simple

[Krob189]

Sorry about that,

Column O came into play because my document has a lot of columns and it was the next free column, Basically I just needed to adjust the IF False to copy what was directly above the row in the new column. Sorry for the edits and thank you both for your help.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[FDibbins]

Happy to help