Microsoft Excel: Rowing logically

By J. Carlton Collins, CPA

Q. I have a massive report that I have imported into Excel, and I want to parse and crunch the data. However, the problem is that the report contains records that, when converted to Excel, are either two or three lines each, as pictured in the example below. What is the best way to convert these two or three lines of data records into single rows?


The initial key to your problem is to create a few logical functions that determine how many rows of data belong to each record, as follows. Examining your data, I noticed that column A contains a product number positioned in the first row of each record, and I was able to key off of that number to create the formula =IF(A2>1,"Yes","No"), which I entered and then copied down column C (as shown below). This simple formula indicates whether a product number appears in column A with either a Yes or No result returned in column C.


Next, in column D, I entered the formula =IF(C2="Yes",1,IF(C2="No",D1+1,0)) and copied it downward (as shown at the bottom of the page). This formula counts the number of rows until the Yes result reappears in column C, and after the Yes result appears, the formula starts recounting.


In column E, I inserted the formula =IF(C2="Yes",MAX(D2:D5),0), which returns the maximum value of the four-row range starting with the adjacent row in column D and including the three rows beneath the adjacent row (as shown below). In essence, this formula indicates how many rows are associated with each product number in column A next to the first row of each record.


Leveraging these results, the remaining formulas are rather simple. Respectively, formulas in columns F, G, H, and I simply return data from column A in the adjacent row, from column B in one row below the adjacent row, from column B two rows below the adjacent row, and from column B three rows below the adjacent row, depending on how many total rows of data there are. The result is that the stacked rows for each record are neatly organized onto a single row for each record, as suggested in the screenshot below


Thereafter, the new data columns can be easily copied and pasted (using Paste, Value) to a new worksheet where the data can be sorted, parsed, and crunched as necessary. You can download this example workbook at

About the author

J. Carlton Collins ( is a technology consultant, a CPE instructor, and a JofA contributing editor.

Note: Instructions for Microsoft Office in “Technology Q&A” refer to the 2007 through 2016 versions, unless otherwise specified.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to We regret being unable to individually answer all submitted questions.


Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.


News quiz: Retirement planning, tax practice, and fraud risk

Recent reports focused on a survey that gauges the worries about retirement among CPA financial planners’ clients, a suit that affects tax practitioners, and a guide that offers advice on fraud risk. See how much you know with this short quiz.


Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.