I have what I thought was a simple task in Excel but for the life of me can’t figure an easy way to do this. Here is the layout that I currently have in my spreadsheet.
Row 1. Main Description of Item 1
Row 2. Quantity of Item 1
Row 3. Size of Item 1
Row 4. Package Type of Item 1
Row 5. -Blank Row-
Row 6. Main Description of Item 2
Row 7. Quantity of Item 2
Row 8. Size of Item 2
Row 9. Package Type of Item 2
I want to replace the above with the following layout so that each Item is in 1 row instead of 4:
Row 1: Main Description of Item 1 in Column A, Qty Size and Package Type of Item 1 in Column B
Row 2: Main Description of Item 2 in Column A, Qty Size and Package Type of Item 2 in Column B
What are the Excel commands that I would need to execute to get this re-formatted?
Thanks, @Kid
there’s probably a macro you can do, but I’m not great at those.
what I would do as a quick and dirty hack is to insert 3 columns to the left, column A would be item number and column B would be “header”. Column C would be a lookup value which combines column A and B. for example, row 2 would have the formula =A2&B2 the result would be “1Description”
label the first 10 rows (spaces show the columns
1 Description 1Description Main Description of Item 1
1 Quantity 1Quantity Quantity of Item 1
1 Size 1Size Size of Item 1
1 Type 1Type Package Type of Item 1
1 Blank 1Blank -Blank Row-
2 Description 2Description Main Description of Item 2
2 Quantity 2Quantity Quantity of Item 2
2 Size 2Size Size of Item 2
2 Type 2Type Package Type of Item 2
2 Blank 2Blank -Blank Row-
Then you can take those first 10 rows and copy the pattern down to the rest of the spreadsheet.
After that, just set up a little vlookup table on a new tab, with the item numbers vertically and headers horizontally. Concatenate the item number and header in the same way eg if the item numbers are in column A and headers are in row 1, the top left data cell in B2 would be =VLOOKUP($A2&B$1,Sheet1!$C:$D,2,FALSE)
copy that across your table and you’re good.
All of this presupposes that the data is consistently 5 lines per item (4 data plus blank)
updated to add "column C to the example table…
additional edit, I just noticed that you wanted Column B to have a couple of different fields contained within it. Personally, I wouldn’t do that unless I needed to, but if you must, then use the concatenate function again to combine columns. eg. =C2&" “&D2&” "&E2
Maybe I’m missing something, but when I try to put the data in, it doesn’t work.
eg, I set up a 20 row column with 4 sets of data (5 rows each). If I try to transpose that 20 row column to a 5x4 matrix, it only transposes the first data set and repeats it on the remaining 3 rows.
the transpose function doesn’t really do it.
that said, there’s probably a way to use the transpose function to more elegantly solve the problem, but the straight-up TRANSPOSE() isn’t getting me there. Unless I’m missing something.
I tried the VLOOKUP and couldn’t figure out what needed to happen.
The Transpose function seems to be what I am looking for (thanks Hokiebird). As long as I have 5 rows on each item, I can copy the Transpose along with any blank rows and it will put the quantity-size-package rows into columns. For those rows that are blank, it puts a 0 in the column.
Then I have copied all data into a separate sheet, pasted the Values and removed the 0’s. I can then use the Concatenate command to combine these 4 columns of data into 1 column.
OK, follow-up question: now when I concatenate and put a comma between each item, how can I indicate to remove the commas for the columns that have no data? I know this is confusing so can clarify this further if necessary.
I must be completely misunderstanding then, cuz when I do the transpose and copy down it’s a jumbled mess unless I copy and paste the row with the formula as well as the 4 blank rows underneath. So there are a lot of blank rows, but if you’re gonna do that then you don’t even need a formula. Just set the first row to each column being = of each row in the data set for the group of 5 rows, then when you copy down, copy the formula row plus the next 4 blank rows. (edit - actually, you can leave off the 5th row, since it’s blank and you aren’t creating an array)
For the extra comma’s, you can use an if(isblank(cell),“”,“,”) instead of just inserting a straight “,” in your concatenate function.
as for the vlookup, all you are doing is creating a unique identifier for each row in your dataset (that’s the 3rd column that was inserted), then the vlookup is trying to find that value (by combining the row and column headers) and pulling the value next to it into your matrix.