VBA Copying data from certain columns one sheet to rows in another sheet

[SamFares]

Hello,

There are two tabs in my Excel Book.  The data source (data copied from) is (Jedi WN joists) tab to be pasted into the rows in (Span=50', Depth=26"-48"). I indicated in column "O" in (Span=50', Depth=26"-48") tab, the column addresses  of (Jedi WN joists) tab that need to be copied  and pasted into (Span=50', Depth=26"-48").  I  showed in row5 &6 of (Span=50', Depth=26"-48") an example  of the data copied from (Jedi WN joists, AD2-AD11) and transposed and pasted into(Span=50', Depth=26"-48").i 'd like to have a macro button that automate the copying and pasting  for the rest of the rows of (Jedi WN joists).  In column P of (Span=50', Depth=26"-48") I indicated the Marks  the corresponding Marks  which are in column A of (Jedi WN joists). I am attaching my excel book.

Thank you in advance!

Sam

[#2]

@SamFares 

Hi, Sam. I notice you've had (as of this writing) 30 views of your message but no replies. I'm going to frustrate you a bit, by also not giving you the macro that you requested.

Part of that is that I generally don't believe in macros; they're OK at times and I use them on rare occasions. But I especially don't believe in them when they're basically just doing by brute force what can be done by elegant Excel functions. And that's my suspicion here; it looks like there must be some relationship between those two sheets that connects the data in the columns with the specific rows you've indicated as targets for the data. I am too unfamiliar with the data, the whole topic (except that we had a contractor in our home yesterday talking of the kind of beam he'd have to put in, recessed, to support the ceiling after we removed the load-bearing wall between our kitchen and dining room).

So I may be totally off base, but this really does look as if a judicious use of functions like MATCH and INDEX or XLOOKUP or FILTER -- each of which is a way to let Excel do the heavy lifting of going and retrieving data according to criteria you set. The criteria in question here in your situation may be embedded or implicit in the column headings, or a combination of some attribute elsewhere in the same row PLUS a column heading.

The point being there's a reason (or a set of reasons) why the data in cells E5 through N5 of your target sheet correspond to the data in column AD2 through AD11 of the source sheet.    If you could articulate the reason(s), then I'm sure somebody here, one of us more familiar with the functions I've mentioned above--or some of the others in that same category--could help you see how Excel could do this more elegantly and accurately based on data and integrity of data. The brute force--even if automated via macro--is more error prone because it's just blindly following orders, "Pick this up from here and put it there; don't think, just do."

[#3 · Accepted solution]

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

[#4]

@mathetes  Thank you for taking the time to explain.

you asked  how "the data in cells E5 through N5 of your target sheet correspond to the data in column AD2 through AD11 of the source sheet." . the data in "Jedi WN Joists" are generated by another software. In column "D" with heading "Description" they describe the steel joist designation. For example, 26LH100/60, indicates the joist depth=26", 28LH300/60 indicates a joist depth =28", and so on. So if you look at column "B" of  " Span=50...", it has the joist depth. So  what are in column AD2:AD11 for joist depth=26 need to be copied  and pasted into E5:N5 which has a joist depth=26.  There 4 pieces of data(Wt, w240,Ix, Top Chord Width) that need to be copied and pasted for each joist depth. Thanks!

[#5]

@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

[#6]

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

[#7]

@Riny_van_Eekelen  Thank you sir!