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?

techqa-3


A.
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.

techqa-4


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.

techqa-5


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.

techqa-6


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
.

techqa-7


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 carltoncollins.com/rows.xlsx.

About the author

J. Carlton Collins (carlton@asaresearch.com) 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 jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.

SPONSORED REPORT

How the election may affect taxation of business income

This report summarizes recent proposals to reform the U.S. business income tax system and considers the path to enactment of any such legislation.

VIDEO

How to Excel pivot a general ledger

The general ledger is a vast historical data archive of your company's financial activities, including revenue, expenses, adjustments, and account balances. J. Carlton Collins, CPA, shows how to prepare data for, and mine data with, PivotTables.

QUIZ

Did you follow 2016’s biggest accounting news?

CPAs will remember 2016 as a year of new standards and new faces. How well did you follow the biggest accounting events? The 7 questions in this quiz will help you find out