Forum Discussion
VBA Copying data from certain columns one sheet to rows in another sheet
SamFares As mathetes suggested, there is no need for VBA here. In the attached file, I have added an INDEX/MATCH formula to find the Joist Depth in the data table (named it JoistData). But, I needed to clean-up both sheets a little bit.
As said, I gave the original "Table134" (sheet Jedi ....) a more descriptive name, JoistData, and added a column to the far right extracting the first two characters of the Description column, so that I could match them with the joist depths in "Span" column B. To make this work, I got rid of the merged cells in column B (they cause nothing but trouble) and copied down the depths for each group of four rows. Then, I made sure that the row headers used in column D (Span) are exactly matching those used in JoistData (Jedi).
The resulting formula in E5 looks like this:
=INDEX(JoistData,MATCH($B5,'Jedi WN Joists'!$AG:$AG,0)+COLUMN()-COLUMN($E$1)-1,MATCH($D5,JoistData[#Headers],0)).... and it was copied down and across.
Now, there is one weakness in your data. You have two groups, both with a depth of 30. If this is indeed correct, the values for the 2nd group will (or may) be incorrect, using the above formula. I'm referring to items J21 through J40 in the JoistData.
Since you already started noting down the applicable ranges to be copied (column O, Span), you perhaps want to continue on that path. Change texts like "AD2-AD11" to "AD2:AD11" to make it look like a reference to a range, so that you can call it with INDIRECT. The following formula transposes the values for each of the 10 rows in a group (Jedi) to a single row with 10 columns. Its demonstrated in rows 56:59 on the Span-sheet.
=TRANSPOSE(INDIRECT("'Jedi WN Joists'!"&O56))Enter the formula with Ctrl-Shift-Enter if your Excel version doesn't not support dynamic array functions.
SamFares As mathetes suggested, there is no need for VBA here. In the attached file, I have added an INDEX/MATCH formula to find the Joist Depth in the data table (named it JoistData). But, I needed to clean-up both sheets a little bit.
As said, I gave the original "Table134" (sheet Jedi ....) a more descriptive name, JoistData, and added a column to the far right extracting the first two characters of the Description column, so that I could match them with the joist depths in "Span" column B. To make this work, I got rid of the merged cells in column B (they cause nothing but trouble) and copied down the depths for each group of four rows. Then, I made sure that the row headers used in column D (Span) are exactly matching those used in JoistData (Jedi).
The resulting formula in E5 looks like this:
=INDEX(JoistData,MATCH($B5,'Jedi WN Joists'!$AG:$AG,0)+COLUMN()-COLUMN($E$1)-1,MATCH($D5,JoistData[#Headers],0)).... and it was copied down and across.
Now, there is one weakness in your data. You have two groups, both with a depth of 30. If this is indeed correct, the values for the 2nd group will (or may) be incorrect, using the above formula. I'm referring to items J21 through J40 in the JoistData.
Since you already started noting down the applicable ranges to be copied (column O, Span), you perhaps want to continue on that path. Change texts like "AD2-AD11" to "AD2:AD11" to make it look like a reference to a range, so that you can call it with INDIRECT. The following formula transposes the values for each of the 10 rows in a group (Jedi) to a single row with 10 columns. Its demonstrated in rows 56:59 on the Span-sheet.
=TRANSPOSE(INDIRECT("'Jedi WN Joists'!"&O56))Enter the formula with Ctrl-Shift-Enter if your Excel version doesn't not support dynamic array functions.
Riny_van_Eekelen Thanks and appreciate you for your detailed answer and providing different options. It is amazing to see the power of Excel.
i'd like to understand the following formula in more detail:
=INDEX(JoistData,MATCH($B6,'Jedi WN Joists'!$AG:$AG,0)+COLUMN()-COLUMN($E$1)-1,MATCH($D6,JoistData[#Headers],0))
1. What does in bold mean or do?
2. The formula in each row is the same. Which part of the formula tells it to take the next the number in the column of "Jedi.." and place it in the next row cell in "Span..."
3. if you have time i would be grateful if you can walk me thru the formula. I understand the function Index and Match but this is a bit over my head.
Thank you!
Sam
SamFares Fair enough. I'll try to explain. The INDEX function has the following structure:
INDEX(range, row, column)
Range is the table you want to find something in, and row and column determine the placement in that range.
For example, if you look for the 1st row and 10th column in the range called JoistData you would want the formula to look like =INDEX(JoistData, 1, 10). In E5, the part before the bolded elements determines that a depth of 26 is found in row 2 of column AD in "Jedi". And this row number 2 has to be "translated" to become the 1st row in the JoistData range as the header row doesn't count. So that will be 2 + 5 (column number for E) - 5 (fixed column number for E, being the first column the this formula is entered into) - 1 = 1. For cell F5 this will become 2 + 6 (column number for F) - 5 (fixed column number for E) - 1 = 2. And so on. For the next group, the depth is found in row 12 and a similar pattern of calculations is made.
Perhaps best if you break-down the entire formula into its individual components and see what values are returned. I hope that this clarifies the matter, although I must admit that it's not always easy to explain such formulae in words.
Riny_van_Eekelen Thank you sir!