0

I often receive rent rolls with the individual charges in rows, and I need to convert them to columns, though the spacing is inconsistent. I tried filling in the unit numbers so that each charge has the applicable unit number in column a, then used index match match, but it isn’t working. Below is an example, though the file can have hundreds of units, so I’m looking for a formula that can pull in the applicable charges based on the unit number. I would think it would be simple, but can’t find an answer.

enter image description here

4
  • 1
    Welcome to SuperUser! Please note we're a bunch of volunteers and we aren't a code-writing service. If you were to show us what research you've done, and what you've tried so far to put this macro together, we'd be happy to advise you on it. – music2myear Jun 11 '19 at 22:54
  • I'd ask the people making your source spreadsheet to generate it in column format. – RonJohn Jun 11 '19 at 22:57
  • I was hoping there would be a formula that I can use. I’m not technical enough to work with code. Agree it would be easy if I could get it in the correct format, but different systems generate the reports in this format, and they can’t change it. – Debra G Jun 11 '19 at 22:59
  • As a partial answer, here's what I'd try: 1. Make a backup copy!! 2. Delete all the blank rows. excel-easy.com/examples/delete-blank-rows.html 3. Add Pet rows which don't exist. There's probably an example macro out there to help with that. 4. Transpose the rent/pet/utilities/total rows. Hopefully this is a helpful start. – RonJohn Jun 11 '19 at 23:10
0

Taking a few liberties with assumptions

Refer to table at end for reference cells/ranges in formulas.

First things first, take the "What you get data" and convert it to "What I'll work with Data". In order to do this just grab some empty space beside the original data or use another sheet if you like. Use the following formula to either take the information in the corresponding cell or if the cell is blank use the information from the cell above. (Assumes the first cell is not blank)

=IF(A3="",H2,A3)

I placed that in cell H3 and copied down and to the right as required. Don't worry about the repeated rows that occur when there is a blank row. That information gets ignored in the end.

That range of H3:L13 I'll call the "What I'll work with data" with row 3 being the header row. Note you will need one row above the header row to get that H2 reference in. or you will have to adjust the formula to suit first row exception.

Now lets look at the what you need. I am going to start with the assumption that you will type in all the headers manually. To get a complete list of what is actually in your list, you could use the built in remove duplicates button and the data ribbon.

enter image description here

Then transpose the results into a row and rearrange the entries to suit your liking (ie. putting Total at the end and starting with Rent which is not an alphabetical sort). Alternately you could do it with the formula for getting the unit numbers I am about to show as an option to the remove duplicates button. You would need to copy, paste values, copy, paste transpose, and then rearrange just like above so I would recommend the more manual approach to set up. once set up though you should not need to go through the process again.

So once you have your header row layed out like you want, use the following array formula to generate a unique list of unit numbers. This method will not not sort your unit numbers for you. (alternatively look at the remove duplicates button mentioned above). I placed the following formula in O4 and copied down to O13:

=IFERROR(INDEX(H:H,MATCH(1,1/((COUNTIF($O$3:O3, $H$4:$H$20)=0)*($H$4:$H$20<>"")),-1)+3),"")

Note this is an array formula (also known as a CSE formula). In order to enter it properly when you go to confirm the formula press CONTROL+SHIFT+ENTER instead of just ENTER. You will know you have done it right when you see { } around your formula in the formula bar. Note that the { } cannot be manually entered around your formula.

Copy cell O4 and paste into O5:O13 or whatever range suits you. just do not include O4 in your paste or you may get an error message about not being allowed to edit part of an array.

in order to get Resident and Market Rent (Columns P and Q) a simple lookup method will work. I am using a combination of INDEX/MATCH wrapped in an IFERROR formual to give empty looking cells when information is not found. I placed the following formula in P4 hen copied down and then to the right.

=IFERROR(INDEX(I:I,MATCH($O4,$H:$H,0)),"")

Now to pull the next set of data (columns R to V), I found the corresponding row number for when the unit number matched and the column header matched. I used the following formula in R4 and copied it down and to the right as needed.

=IFERROR(INDEX($L:$L,AGGREGATE(14,6,ROW($H$4:$H$13)/(($H$4:$H$13=$O4)*($K$4:$K$13=R$3)),1)),"")

Note that AGGREGATE performs array like operations within its brackets. As a result, do not use full column/row references like H:H or 3:3 or a lot of excess calculations can be made which can bog down your computer. Not too bad for a single cell calculation, but when the formula gets copied multiple times it can get ugly.

POC

NOTES:

  • no duplicate unit numbers with different resident names. only first unit name combo will get listed
  • Cell O7 with "103" was a manual entry to demonstrate the option of manually entering unit numbers and demonstrating blank row results for unit not in list. Similar to column U and manually entering Cable which is not in the list.
  • Column V "Total" is actually a look up using the Aggregate. It might be preferable to change that to a SUM formula.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.