Move data to a different row, contingent upon data in another column

JonnyOctagon it might be helpful if you would upload an example in an Excel workbook to the forum. (no pics or screenshots ... saves retyping data). Be sure to desensitize the data.

Include a small section of BEFORE and another AFTER showing what you want.

If you are not familiar with how to do this:
To attach a file to your post,

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

Failing to see the logic behind the AFTER workbook is it possible BEFORE/AFTER are the other way around?

If this is doable by formula I am not finding a workable approach.

It may be doable with VBA. Unfortunately I am VBA 'lite'.

We'll see if anyone comes up with a solution.

In the meantime I'm sending up a "flare" to the rest of the community.

As FlameRetired noted, this is doable with VBA, but if you'd prefer formulas and some minor legwork, I think you can also get your results with a helper column. Namely, in N3 of my attachment, I use:

=IF($C3<>"",INDEX($F3:$F50,MATCH("rent",$E3:$E50,0)),"")

...and fill down to return the next "rent" entry each time there's data in column C. You can then copy the results and paste the values in column N or F and the rent should sort alongside a sorting of column C. In the attached, I've done the formula bit but not the paste values; see if it'll work for you or if you'd rather use a VBA solution:

Here's a Pivot Table/hybrid option, which will also require a little leg work.
To make proper records out of the table on the before sheet, you will need to fill in the missing unit numbers in column A. This can be done using the method shown in the video in the second second section of this article.
A column header, Fee, is also needed for column E. The report filter for the PT is based on this column.
The rent amount will need to be the first column of the PT, so that it will be the primary key for sorting. The second column of the PT is the Unit number. The rest of the information is then displayed using the formula:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Try this:

Please Login or Register  to view this content.

Hi Johnny,

Thanks for the rep!

Did you try my program?

I think it's the simplest solution

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

Originally Posted by xladept

Hi Johnny,

Thanks for the rep!

Did you try my program?

I think it's the simplest solution

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

It may very well be! I just have 0 knowledge of/experience with macros (is it even a macro?? I don't even know!!) and I fear what I do not know...

I should have realized that since I picked it up from the "flare" by FlameRetired - sorry!

If you haven't used macros before you'll need to go to:
File- options - trust center -trust center settings - macro settings ,
the second option down (disable all macros with notification)

Then - Copy the code to the clipboard

Open your Workbook

Press ALT + F11 to open the Visual Basic Editor.

Select "Module" from the Insert menu

Type "Option Explicit" then paste the code under it

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name
*Be sure to save the book with the code as Macro-Enabled

I'm glad I could help, thanks for the rep!

For what it's worth, if you're able to use macros in this document, it might be worth experimenting with xladept's solution. It definitely saves a little bit of legwork. Keep a backup save and you can always resort to either of the other two solutions if you decide you'd rather use something you have more experience implementing and adapting.

xladept appears to be offline, so I hope I don't offend by jumping in - the code looks in column A for non-blank entries in order to identify the first row. It doesn't rely on the sq. ft. column at all. It then transports whatever is in columns E and F up to that row when it finds a "rent" match. If the "rent" column is going to be somewhere other than column E, then change the three instances of the number "5" in xladept's code (in the IF clause near the bottom) to the number that corresponds to the column in which the term "rent" will be found.

Hope that helps?

I'll comment:

Please Login or Register  to view this content.

You're welcome!

And - Thanks for the rep!

Originally Posted by bfret333

Looking to follow up on this 5 year old conversation.
This only works because "Rent" Occurs in ever resident charge. Does anyone know how you can do this for the other charge codes that only occur on a select amount of residents? Without VB please.

For example I used provided formula here for "Furniture Rent" for a different rent roll

=IF($D8<>"",INDEX($K8:$K3904,MATCH("Furniture Rent",$I8:$I3904,0)),"")

.. But not all residents have furniture. If this can be done, i would then be able to run a pivot table to know which unit type has furniture based on the charge code "furniture rent".

Attachment 766129

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread