1. column
  2. TECHNOLOGY Q&A

Microsoft Excel: Rowing logically

By J. Carlton Collins, CPA

Please note: This item is from our archives and was published in 2016. It is provided for historical reference. The content may be out of date and links may no longer function.

TOPICS

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

Advertisement

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.