Macros for Extracting Data from Multiple Worksheets for a Mastersheet

B

bck

New Member
Joined
Sep 22, 2009
Messages
7
Hi there,

I'm trying to synthesize a fair amount of averages response time data from about 300 separate worksheets into one master sheet and I'm wondering what the best way is to do it using Excel's macros.

In each subject condition-worksheet there are 24 cell items that need to be pulled and put into the master worksheet; there are 2 conditions, for a total of 48 line items.

In each subject file, the subject number is in cell A1, my first desired average is in M7, then M15, M23, M31, M39, M47, M55, M63, then it repeats back to M9, M17, M25, etc..

How can I create a macro that creates a new line for each subject (starting with row 2) first with their subject number (A1 in all subject files), then M7, M15, M23, M31, M39, etc. moving across the row?

Any help would be greatly appreciated!

Thanks,
BCK
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Please clarify whether those 300 'worksheets' are actually worksheets (i.e. tabs) contained in one workbook or rather workbooks (i.e. separate Excel files). Separate files are nowadays referred to as workbooks and no longer as worksheets (as they were in the (by now rather distant) past ...
 
Upvote 0
Apologies, yes, they are all separate workbooks, each with one worksheet (Sheet1).
 
Upvote 0
Hi
Paste the following codes in the macro window ( alt F11) of a new workbook and save it inside the folder containing 300+ workbooks. run the macro.
Code: 
Sub BCK()
Dim z  As Long, e As Long, g As Long
Dim f As String, h As String
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
Cells(2, 1).Select
f = Dir(Cells(1, 2) & "*.xls")
    Do While Len(f) > 0
    ActiveCell.Formula = f
    ActiveCell.Offset(1, 0).Select
    f = Dir()
    Loop
z = Cells(Rows.Count, 1).End(xlUp).Row
    For e = 3 To z
        For g = 1 To 9
        h = Choose(g, "A1", "M7", "M15", "M23", "M31", "M39", "M47", "M55", "M63")
        Cells(2, g + 1) = h
        Cells(1, 3) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Sheet1'!" & h
        Cells(e, g + 1) = Cells(1, 3)
        Next g
    Next e
Range("A1:C1").Delete
MsgBox "collating is complete."
End Sub
Ravi
 
Upvote 0
Ravi, Thanks so much for your help. I've tried implementing your fix with only minimal success in tweaking it to my data, although I'm new at VB coding! Thanks for bearing with me. Here are some more specifics, if they help:

The drive tree is set up so that the master list is in, say, Folder A. Then within folder A are two folders for the two conditions.
So:
HTML: 
Folder A
     MasterList (file)
     Condition 1 (folder)
          Subj1Cond1 (file)
          Subj2Cond1 (file)
     Condition 2 (folder)
          Subj1Cond2 (file)
          Subj2Cond2 (file)
I'm looking to open the masterlist, then open each subject file and grab a set of cells to paste into one row per subject in the master file.

So, for example:
HTML: 
MasterList:
SubjName Condition1Avg1 Condition1Avg2 Condition2Avg1 Condition2Avg2
1        12             14             15             14
2        13             12             15             17
The cells I wish to grab are the subject number (A1 in all files), then M7, M15, M23, M31, M39, M47, M55, M63, M9, M17, M25, M33, M41, M49, M57, M65, M11, M19, M27, M35, M43, M51, M59, M67
So, I'd like to grab A1, then:
Code: 
x = 7
While x<67
grab Mx
if x = 63
   x = 9
if x = 65
   x = 11
x = x + 8
End
(or something like this)
So, the final output line for the MasterList would be:
A1, M7, M15, M23, M31, M39, M47, M55, M63, M9, M17, M25, M33, M41, M49, M57, M65, M11, M19, M27, M35, M43, M51, M59, M67

Getting this data exported to the master list is the first hurdle.
The big problem comes when, for example, Subject 2's Condition1 and Condition2 data are in different files (and different folders, but I could always change the drive structure if it makes the scripting easier).

So, from above:
HTML: 
MasterList:
SubjName Condition1Avg1 Condition1Avg2 Condition2Avg1 Condition2Avg2
1       [12             14] one file  [15             14] second file
2       [13             12]           [15             17]
The question then becomes, is there a way to have the macro look at the subject number (A1) and see if it exists in column A already? If so, then have it start putting in the data from condition 2 in the first empty cell on that subject's line.

I hope this makes sense. Please let me know if I could clarify anything for you.

Thank you so much!
 
Upvote 0
Responding to a few PMs on this as a heretofore innocent bystander - -

Trying to visualize the set-up and not getting it.

Please answer Yes or No to the following statements, and if No, explain.

(1)
You have a Master workbook where this macro will be run from.

(2)
The worksheet in the Master workbook where the data will be collected is named "CollectionHubbaHubba" (I know the answer to this will be No, I needed to get your attention to tell us the name of the collection worksheet in the Master workbook).

(3)
Whatever data you collect will be fresh new data starting in row 2, meaning, any data that was collected there from a previous collection attempt will be deleted and replaced by each new macro run.

(4)
There are 24 source workbooks, one for each Subject, each workbook holding data for two conditions.

(5)
There are 48 source workbooks, one for each Subject and Condition.

(6)
Each source workbook contains one worksheet.

(7)
The name of the worksheet is "HowdyDoody". I know the answer to this will be No, I needed to get your attention to tell us the name of the source worksheets in each source workbook, IF each source workbook has more than one worksheet. OTHERWISE, if each source workbook only have ONE SINGLE worksheet, just say so and that will solve this part of where the macro is supposed to look when extracting data from these source workbooks.

(8)
All the source workbooks are in one common folder.



OK, that's it for the Yes and No section.

Now the "what do you mean" section --

What do you mean by...

MasterList:
Code: 
SubjName Condition1Avg1 Condition1Avg2 Condition2Avg1 Condition2Avg2
1 12 14 15 14
2 13 12 15 17

...which only spans 4 data columns

and this...
"How can I create a macro that creates a new line for each subject (starting with row 2) first with their subject number (A1 in all subject files), then M7, M15, M23, M31, M39, etc. moving across the row?"

...which spans more than 5 columns (maybe more than 10 columns with each condition).

What are the header labels in row 1 of the Master workbook's collection worksheet.


Consider copying a representative selection of the data you want to import (that would be from one of your source files) and pasting it directly onto this web page as part of the Reply (be sure to visually include row and column references)

and

Copy and paste a mock-up of your expected results on the collection worksheet of the Master workbook.

That way it'll be clearer to understand your workbooks' set-up.
 
Last edited:
Upvote 0
Hi Tom,

Thanks for helping out.

1) Yes.

2) The worksheet in the MasterList workbook is called "Words"

3) Sort of. The idea is that there is already existing data in the MasterList workbook and any new data will begin on the first available empty row on the worksheet "Words".
So: Row 1 = Title Row; Row 2 = First subject's data; Row 3 = Second subject's data; etc.

4) No. There are 2 workbooks for each subject, each workbook has only one worksheet; for each worksheet, there are 24 cells of information I need to add to the MasterList workbook, all on one row.
So, for example, subject 5 has two workbook files, let's say 5wordbig.xls and 5wordsmall.xls. I'm looking to enter it into the MasterList like so:
New Row: [5](Subject Name pulled from "A1" of the either file),[24 cells from 5wordbig.xls],[24 cells pulled from 5wordsmall.xls]

5) No. I think this was covered above...might need more clarification.

6) Yes.

7) No. The name of the source workbooks, each with only ONE worksheet (Sheet1) are:
6word_LZ297
6word_LZ299
for condition1, etc. and
7word_LZ297
7word_LZ299
for condition2, etc.

8) No. Right now each condition is in its own folder, but if it's easier for programming, I can dump them all into one 'Data' folder. As it is now, the tree structure looks like this:


HTML: 
Folder A
     MasterList (file)
     Condition 1 (folder)
          Subj1Cond1 (file)
          Subj2Cond1 (file)
     Condition 2 (folder)
          Subj1Cond2 (file)
          Subj2Cond2 (file)
Now the 'what do I mean:

--The
HTML: 
MasterList:
SubjName Condition1Avg1 Condition1Avg2 Condition2Avg1 Condition2Avg2
1        12             14             15             14
2        13             12             15             17
was meant to give an idea of what the MasterList worksheet looks like in a basic, abbreviated form.
So, the actual file has the subject number as column A, then 48 items pulled from the data files. I was trying to simplify for the sake of computation of the post, but the whole picture is this:

There are two variable for the analysis, each with two conditions:
Words: Bigger and Smaller
Images: Bigger and Smaller
The MasterList, worksheet "Words", ideally, has:
Subject Name, the avgs etc from Words Bigger, the avgs etc from Words Smaller
Then, on a separate tab ("Images"):
Subject Name, the avgs etc from Images Bigger, the avgs etc from Images Smaller
If you wanted to try and tackle both variables in one task, fantastic, but I figured I'd get a basic sense and then try and adjust the parameters for the Images task myself. I'm happy to go into more depth regarding the second variable, if need be.

--Correct, there are 58 columns (subject name, diagnosis, 48 cells from 2 condition workbooks, 8 avgs based off 48 cells)

The header labels for Row 1 are:
<table x:str="" style="border-collapse: collapse; width: 3953pt;" width="5273" border="0" cellpadding="0" cellspacing="0"><col style="width: 66pt;" width="88" span="50"><col style="width: 77pt;" width="103" span="4"><col style="width: 114pt;" width="152"><col style="width: 77pt;" width="103" span="3"><tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 66pt;" width="88" height="17">Subject</td> <td style="width: 66pt;" width="88">Diagnosis</td> <td style="width: 66pt;" width="88">6_AvgRT_1</td> <td style="width: 66pt;" width="88">6_AvgRT_2</td> <td style="width: 66pt;" width="88">6_AvgRT_3</td> <td style="width: 66pt;" width="88">6_AvgRT_4</td> <td style="width: 66pt;" width="88">6_AvgRT_5</td> <td style="width: 66pt;" width="88">6_AvgRT_6</td> <td style="width: 66pt;" width="88">6_AvgRT_7</td> <td style="width: 66pt;" width="88">6_AvgRT_8</td> <td style="width: 66pt;" width="88">6_MedRT_1</td> <td style="width: 66pt;" width="88">6_MedRT_2</td> <td style="width: 66pt;" width="88">6_MedRT_3</td> <td style="width: 66pt;" width="88">6_MedRT_4</td> <td style="width: 66pt;" width="88">6_MedRT_5</td> <td style="width: 66pt;" width="88">6_MedRT_6</td> <td style="width: 66pt;" width="88">6_MedRT_7</td> <td style="width: 66pt;" width="88">6_MedRT_8</td> <td style="width: 66pt;" width="88">6_PropCorr_1</td> <td style="width: 66pt;" width="88">6_PropCorr_2</td> <td style="width: 66pt;" width="88">6_PropCorr_3</td> <td style="width: 66pt;" width="88">6_PropCorr_4</td> <td style="width: 66pt;" width="88">6_PropCorr_5</td> <td style="width: 66pt;" width="88">6_PropCorr_6</td> <td style="width: 66pt;" width="88">6_PropCorr_7</td> <td style="width: 66pt;" width="88">6_PropCorr_8</td> <td style="width: 66pt;" width="88">7_AvgRT_1</td> <td style="width: 66pt;" width="88">7_AvgRT_2</td> <td style="width: 66pt;" width="88">7_AvgRT_3</td> <td style="width: 66pt;" width="88">7_AvgRT_4</td> <td style="width: 66pt;" width="88">7_AvgRT_5</td> <td style="width: 66pt;" width="88">7_AvgRT_6</td> <td style="width: 66pt;" width="88">7_AvgRT_7</td> <td style="width: 66pt;" width="88">7_AvgRT_8</td> <td style="width: 66pt;" width="88">7_MedRT_1</td> <td style="width: 66pt;" width="88">7_MedRT_2</td> <td style="width: 66pt;" width="88">7_MedRT_3</td> <td style="width: 66pt;" width="88">7_MedRT_4</td> <td style="width: 66pt;" width="88">7_MedRT_5</td> <td style="width: 66pt;" width="88">7_MedRT_6</td> <td style="width: 66pt;" width="88">7_MedRT_7</td> <td style="width: 66pt;" width="88">7_MedRT_8</td> <td style="width: 66pt;" width="88">7_PropCorr_1</td> <td style="width: 66pt;" width="88">7_PropCorr_2</td> <td style="width: 66pt;" width="88">7_PropCorr_3</td> <td style="width: 66pt;" width="88">7_PropCorr_4</td> <td style="width: 66pt;" width="88">7_PropCorr_5</td> <td style="width: 66pt;" width="88">7_PropCorr_6</td> <td style="width: 66pt;" width="88">7_PropCorr_7</td> <td style="width: 66pt;" width="88">7_PropCorr_8</td> <td class="xl24" style="width: 77pt;" width="103">6a_1v8_medRT</td> <td class="xl24" style="width: 77pt;" width="103">6a_8v1_acc</td> <td class="xl24" style="width: 77pt;" width="103">7a_1v8_medRT</td> <td class="xl24" style="width: 77pt;" width="103">7a_8v1_acc</td> <td class="xl24" style="width: 114pt;" width="152">6a_1-2v3-8_medRT</td> <td class="xl24" style="width: 77pt;" width="103">6a_3-8v1-2_acc</td> <td class="xl24" style="width: 77pt;" width="103">7a_1-2v3-8_medRT</td> <td class="xl24" style="width: 77pt;" width="103">7a_3-8v1-2_acc</td> </tr></tbody></table>
The last 8 columns are averages of the averages, so don't worry about those (I hope this doesn't confuse you more--if it does, please disregard).

And lastly, here's a mockup, I hope the formatting comes through:
<table x:str="" style="border-collapse: collapse; width: 3938pt;" width="5253" border="0" cellpadding="0" cellspacing="0"><col style="width: 66pt;" width="88"> <col style="width: 51pt;" width="68"> <col style="width: 66pt;" width="88" span="48"> <col style="width: 77pt;" width="103" span="4"> <col style="width: 114pt;" width="152"> <col style="width: 77pt;" width="103" span="3"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 66pt;" width="88" height="17">Subject</td> <td style="width: 51pt;" width="68">Diagnosis</td> <td style="width: 66pt;" width="88">6_AvgRT_1</td> <td style="width: 66pt;" width="88">6_AvgRT_2</td> <td style="width: 66pt;" width="88">6_AvgRT_3</td> <td style="width: 66pt;" width="88">6_AvgRT_4</td> <td style="width: 66pt;" width="88">6_AvgRT_5</td> <td style="width: 66pt;" width="88">6_AvgRT_6</td> <td style="width: 66pt;" width="88">6_AvgRT_7</td> <td style="width: 66pt;" width="88">6_AvgRT_8</td> <td style="width: 66pt;" width="88">6_MedRT_1</td> <td style="width: 66pt;" width="88">6_MedRT_2</td> <td style="width: 66pt;" width="88">6_MedRT_3</td> <td style="width: 66pt;" width="88">6_MedRT_4</td> <td style="width: 66pt;" width="88">6_MedRT_5</td> <td style="width: 66pt;" width="88">6_MedRT_6</td> <td style="width: 66pt;" width="88">6_MedRT_7</td> <td style="width: 66pt;" width="88">6_MedRT_8</td> <td style="width: 66pt;" width="88">6_PropCorr_1</td> <td style="width: 66pt;" width="88">6_PropCorr_2</td> <td style="width: 66pt;" width="88">6_PropCorr_3</td> <td style="width: 66pt;" width="88">6_PropCorr_4</td> <td style="width: 66pt;" width="88">6_PropCorr_5</td> <td style="width: 66pt;" width="88">6_PropCorr_6</td> <td style="width: 66pt;" width="88">6_PropCorr_7</td> <td style="width: 66pt;" width="88">6_PropCorr_8</td> <td style="width: 66pt;" width="88">7_AvgRT_1</td> <td style="width: 66pt;" width="88">7_AvgRT_2</td> <td style="width: 66pt;" width="88">7_AvgRT_3</td> <td style="width: 66pt;" width="88">7_AvgRT_4</td> <td style="width: 66pt;" width="88">7_AvgRT_5</td> <td style="width: 66pt;" width="88">7_AvgRT_6</td> <td style="width: 66pt;" width="88">7_AvgRT_7</td> <td style="width: 66pt;" width="88">7_AvgRT_8</td> <td style="width: 66pt;" width="88">7_MedRT_1</td> <td style="width: 66pt;" width="88">7_MedRT_2</td> <td style="width: 66pt;" width="88">7_MedRT_3</td> <td style="width: 66pt;" width="88">7_MedRT_4</td> <td style="width: 66pt;" width="88">7_MedRT_5</td> <td style="width: 66pt;" width="88">7_MedRT_6</td> <td style="width: 66pt;" width="88">7_MedRT_7</td> <td style="width: 66pt;" width="88">7_MedRT_8</td> <td style="width: 66pt;" width="88">7_PropCorr_1</td> <td style="width: 66pt;" width="88">7_PropCorr_2</td> <td style="width: 66pt;" width="88">7_PropCorr_3</td> <td style="width: 66pt;" width="88">7_PropCorr_4</td> <td style="width: 66pt;" width="88">7_PropCorr_5</td> <td style="width: 66pt;" width="88">7_PropCorr_6</td> <td style="width: 66pt;" width="88">7_PropCorr_7</td> <td style="width: 66pt;" width="88">7_PropCorr_8</td> <td class="xl25" style="width: 77pt;" width="103">6a_1v8_medRT</td> <td class="xl25" style="width: 77pt;" width="103">6a_8v1_acc</td> <td class="xl25" style="width: 77pt;" width="103">7a_1v8_medRT</td> <td class="xl25" style="width: 77pt;" width="103">7a_8v1_acc</td> <td class="xl25" style="width: 114pt;" width="152">6a_1-2v3-8_medRT</td> <td class="xl25" style="width: 77pt;" width="103">6a_3-8v1-2_acc</td> <td class="xl25" style="width: 77pt;" width="103">7a_1-2v3-8_medRT</td> <td class="xl25" style="width: 77pt;" width="103">7a_3-8v1-2_acc</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">LZ170</td> <td x:num="" align="right">1</td> <td class="xl24" x:num="3254.6669999999999" align="right">3254.67</td> <td class="xl24" x:num="2717.875" align="right">2717.88</td> <td class="xl24" x:num="1853.625" align="right">1853.63</td> <td class="xl24" x:num="" align="right">2105.75</td> <td class="xl24" x:num="2053.2860000000001" align="right">2053.29</td> <td class="xl24" x:num="" align="right">1929.00</td> <td class="xl24" x:num="" align="right">2313.00</td> <td class="xl24" x:num="" align="right">2469.00</td> <td class="xl24" x:num="" align="right">3055.00</td> <td class="xl24" x:num="" align="right">2448.00</td> <td class="xl24" x:num="" align="right">1906.00</td> <td class="xl24" x:num="" align="right">2096.00</td> <td class="xl24" x:num="" align="right">1690.00</td> <td class="xl24" x:num="" align="right">1959.00</td> <td class="xl24" x:num="" align="right">2077.50</td> <td class="xl24" x:num="" align="right">1985.00</td> <td class="xl24" x:num="" align="right">0.75</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="0.875" align="right">0.88</td> <td class="xl24" x:num="0.875" align="right">0.88</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="2064.875" align="right">2064.88</td> <td class="xl24" x:num="1859.857" align="right">1859.86</td> <td class="xl24" x:num="1797.375" align="right">1797.38</td> <td class="xl24" x:num="1820.375" align="right">1820.38</td> <td class="xl24" x:num="1720.125" align="right">1720.13</td> <td class="xl24" x:num="" align="right">1940.50</td> <td class="xl24" x:num="" align="right">1828.75</td> <td class="xl24" x:num="1756.625" align="right">1756.63</td> <td class="xl24" x:num="" align="right">1873.50</td> <td class="xl24" x:num="" align="right">1963.00</td> <td class="xl24" x:num="" align="right">1813.50</td> <td class="xl24" x:num="" align="right">2007.50</td> <td class="xl24" x:num="" align="right">1658.00</td> <td class="xl24" x:num="" align="right">1953.00</td> <td class="xl24" x:num="" align="right">1769.50</td> <td class="xl24" x:num="" align="right">1718.00</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="0.875" align="right">0.88</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl26" x:num="" x:fmla="=K2-R2" align="right">1070.00</td> <td class="xl26" x:num="" x:fmla="=Z2-S2" align="right">0.25</td> <td class="xl26" x:num="" x:fmla="=AI2-AP2" align="right">155.50</td> <td class="xl26" x:num="" x:fmla="=AX2-AQ2" align="right">0.00</td> <td class="xl26" x:num="" x:fmla="=AVERAGE(K2:L2)-AVERAGE(M2:R2)" align="right">799.25</td> <td class="xl26" x:num="8.333333333333337E-2" x:fmla="=AVERAGE(U2:Z2)-AVERAGE(S2:T2)" align="right">0.08</td> <td class="xl26" x:num="98.333333333333258" x:fmla="=AVERAGE(AI2:AJ2)-AVERAGE(AK2:AP2)" align="right">98.33</td> <td class="xl26" x:num="6.25E-2" x:fmla="=AVERAGE(AS2:AX2)-AVERAGE(AQ2:AR2)" align="right">0.06</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">LZ182</td> <td x:num="" align="right">3</td> <td class="xl24" x:num="" align="right">2255.00</td> <td class="xl24" x:num="" align="right">2741.75</td> <td class="xl24" x:num="2409.5709999999999" align="right">2409.57</td> <td class="xl24" x:num="2214.857" align="right">2214.86</td> <td class="xl24" x:num="2082.4290000000001" align="right">2082.43</td> <td class="xl24" x:num="" align="right">2360.50</td> <td class="xl24" x:num="" align="right">2379.50</td> <td class="xl24" x:num="2100.625" align="right">2100.63</td> <td class="xl24" x:num="" align="right">1947.00</td> <td class="xl24" x:num="" align="right">2749.50</td> <td class="xl24" x:num="" align="right">2621.00</td> <td class="xl24" x:num="" align="right">2113.00</td> <td class="xl24" x:num="" align="right">2132.00</td> <td class="xl24" x:num="" align="right">2136.00</td> <td class="xl24" x:num="" align="right">1958.00</td> <td class="xl24" x:num="" align="right">2280.00</td> <td class="xl24" x:num="0.875" align="right">0.88</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="0.875" align="right">0.88</td> <td class="xl24" x:num="0.875" align="right">0.88</td> <td class="xl24" x:num="0.875" align="right">0.88</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="" align="right">2229.50</td> <td class="xl24" x:num="2405.6669999999999" align="right">2405.67</td> <td class="xl24" x:num="" align="right">2319.00</td> <td class="xl24" x:num="2348.5709999999999" align="right">2348.57</td> <td class="xl24" x:num="1894.5709999999999" align="right">1894.57</td> <td class="xl24" x:num="1837.875" align="right">1837.88</td> <td class="xl24" x:num="2217.375" align="right">2217.38</td> <td class="xl24" x:num="" align="right">1896.25</td> <td class="xl24" x:num="" align="right">2299.50</td> <td class="xl24" x:num="" align="right">2398.00</td> <td class="xl24" x:num="" align="right">2441.50</td> <td class="xl24" x:num="" align="right">2589.00</td> <td class="xl24" x:num="" align="right">1971.00</td> <td class="xl24" x:num="" align="right">1703.50</td> <td class="xl24" x:num="" align="right">1980.50</td> <td class="xl24" x:num="" align="right">2102.50</td> <td class="xl24" x:num="" align="right">0.75</td> <td class="xl24" x:num="" align="right">0.75</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="0.875" align="right">0.88</td> <td class="xl24" x:num="0.875" align="right">0.88</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl26" x:num="" x:fmla="=K3-R3" align="right">-333.00</td> <td class="xl26" x:num="0.125" x:fmla="=Z3-S3" align="right">0.13</td> <td class="xl26" x:num="" x:fmla="=AI3-AP3" align="right">197.00</td> <td class="xl26" x:num="" x:fmla="=AX3-AQ3" align="right">0.25</td> <td class="xl26" x:num="141.58333333333348" x:fmla="=AVERAGE(K3:L3)-AVERAGE(M3:R3)" align="right">141.58</td> <td class="xl26" x:num="" x:fmla="=AVERAGE(U3:Z3)-AVERAGE(S3:T3)" align="right">0.00</td> <td class="xl26" x:num="217.41666666666652" x:fmla="=AVERAGE(AI3:AJ3)-AVERAGE(AK3:AP3)" align="right">217.42</td> <td class="xl26" x:num="0.20833333333333337" x:fmla="=AVERAGE(AS3:AX3)-AVERAGE(AQ3:AR3)" align="right">0.21</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">LZ190</td> <td x:num="" align="right">2</td> <td class="xl24" x:num="3686.3330000000001" align="right">3686.33</td> <td class="xl24" x:num="" align="right">3836.50</td> <td class="xl24" x:num="3440.875" align="right">3440.88</td> <td class="xl24" x:num="3196.4290000000001" align="right">3196.43</td> <td class="xl24" x:num="" align="right">2989.25</td> <td class="xl24" x:num="" align="right">3108.25</td> <td class="xl24" x:num="3321.625" align="right">3321.63</td> <td class="xl24" x:num="3177.7139999999999" align="right">3177.71</td> <td class="xl24" x:num="" align="right">3569.00</td> <td class="xl24" x:num="" align="right">3883.00</td> <td class="xl24" x:num="" align="right">3438.00</td> <td class="xl24" x:num="" align="right">2905.00</td> <td class="xl24" x:num="" align="right">2784.00</td> <td class="xl24" x:num="" align="right">2974.00</td> <td class="xl24" x:num="" align="right">2965.00</td> <td class="xl24" x:num="" align="right">2586.00</td> <td class="xl24" x:num="0.375" align="right">0.38</td> <td class="xl24" x:num="" align="right">0.50</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="0.875" align="right">0.88</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="0.875" align="right">0.88</td> <td class="xl24" x:num="2613.143" align="right">2613.14</td> <td class="xl24" x:num="3233.875" align="right">3233.88</td> <td class="xl24" x:num="" align="right">2701.50</td> <td class="xl24" x:num="2583.375" align="right">2583.38</td> <td class="xl24" x:num="2247.875" align="right">2247.88</td> <td class="xl24" x:num="" align="right">2053.50</td> <td class="xl24" x:num="2064.875" align="right">2064.88</td> <td class="xl24" x:num="2336.625" align="right">2336.63</td> <td class="xl24" x:num="" align="right">2440.00</td> <td class="xl24" x:num="" align="right">3167.00</td> <td class="xl24" x:num="" align="right">2768.00</td> <td class="xl24" x:num="" align="right">2594.00</td> <td class="xl24" x:num="" align="right">2249.50</td> <td class="xl24" x:num="" align="right">1955.50</td> <td class="xl24" x:num="" align="right">2089.50</td> <td class="xl24" x:num="" align="right">2302.50</td> <td class="xl24" x:num="0.875" align="right">0.88</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl26" x:num="" x:fmla="=K4-R4" align="right">983.00</td> <td class="xl26" x:num="" x:fmla="=Z4-S4" align="right">0.50</td> <td class="xl26" x:num="" x:fmla="=AI4-AP4" align="right">137.50</td> <td class="xl26" x:num="0.125" x:fmla="=AX4-AQ4" align="right">0.13</td> <td class="xl26" x:num="" x:fmla="=AVERAGE(K4:L4)-AVERAGE(M4:R4)" align="right">784.00</td> <td class="xl26" x:num="0.52083333333333337" x:fmla="=AVERAGE(U4:Z4)-AVERAGE(S4:T4)" align="right">0.52</td> <td class="xl26" x:num="" x:fmla="=AVERAGE(AI4:AJ4)-AVERAGE(AK4:AP4)" align="right">477.00</td> <td class="xl26" x:num="6.25E-2" x:fmla="=AVERAGE(AS4:AX4)-AVERAGE(AQ4:AR4)" align="right">0.06</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">LZ213</td> <td x:num="" align="right">3</td> <td class="xl24" x:num="" align="right">3629.50</td> <td class="xl24" x:num="4106.5709999999999" align="right">4106.57</td> <td class="xl24" x:num="2472.1669999999999" align="right">2472.17</td> <td class="xl24" x:num="3384.5709999999999" align="right">3384.57</td> <td class="xl24" x:num="3760.875" align="right">3760.88</td> <td class="xl24" x:num="" align="right">2604.00</td> <td class="xl24" x:num="" align="right">3256.00</td> <td class="xl24" x:num="3309.3330000000001" align="right">3309.33</td> <td class="xl24" x:num="" align="right">3421.00</td> <td class="xl24" x:num="" align="right">4255.00</td> <td class="xl24" x:num="" align="right">2232.50</td> <td class="xl24" x:num="" align="right">2945.00</td> <td class="xl24" x:num="" align="right">3522.00</td> <td class="xl24" x:num="" align="right">2304.50</td> <td class="xl24" x:num="" align="right">3273.00</td> <td class="xl24" x:num="" align="right">2860.50</td> <td class="xl24" x:num="" align="right">0.75</td> <td class="xl24" x:num="0.875" align="right">0.88</td> <td class="xl24" x:num="" align="right">0.75</td> <td class="xl24" x:num="0.875" align="right">0.88</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="0.875" align="right">0.88</td> <td class="xl24" x:num="" align="right">0.75</td> <td class="xl24" x:num="2787.2860000000001" align="right">2787.29</td> <td class="xl24" x:num="2949.1669999999999" align="right">2949.17</td> <td class="xl24" x:num="2152.6669999999999" align="right">2152.67</td> <td class="xl24" x:num="" align="right">2688.50</td> <td class="xl24" x:num="" align="right">2097.00</td> <td class="xl24" x:num="2178.5709999999999" align="right">2178.57</td> <td class="xl24" x:num="2397.7139999999999" align="right">2397.71</td> <td class="xl24" x:num="2755.375" align="right">2755.38</td> <td class="xl24" x:num="" align="right">2780.00</td> <td class="xl24" x:num="" align="right">2378.00</td> <td class="xl24" x:num="" align="right">2048.00</td> <td class="xl24" x:num="" align="right">2450.50</td> <td class="xl24" x:num="" align="right">1799.00</td> <td class="xl24" x:num="" align="right">2135.00</td> <td class="xl24" x:num="" align="right">2278.00</td> <td class="xl24" x:num="" align="right">2375.00</td> <td class="xl24" x:num="0.875" align="right">0.88</td> <td class="xl24" x:num="" align="right">0.75</td> <td class="xl24" x:num="" align="right">0.75</td> <td class="xl24" x:num="" align="right">0.75</td> <td class="xl24" x:num="0.625" align="right">0.63</td> <td class="xl24" x:num="0.875" align="right">0.88</td> <td class="xl24" x:num="0.875" align="right">0.88</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl26" x:num="" x:fmla="=K5-R5" align="right">560.50</td> <td class="xl26" x:num="" x:fmla="=Z5-S5" align="right">0.00</td> <td class="xl26" x:num="" x:fmla="=AI5-AP5" align="right">405.00</td> <td class="xl26" x:num="0.125" x:fmla="=AX5-AQ5" align="right">0.13</td> <td class="xl26" x:num="" x:fmla="=AVERAGE(K5:L5)-AVERAGE(M5:R5)" align="right">981.75</td> <td class="xl26" x:num="6.25E-2" x:fmla="=AVERAGE(U5:Z5)-AVERAGE(S5:T5)" align="right">0.06</td> <td class="xl26" x:num="398.08333333333348" x:fmla="=AVERAGE(AI5:AJ5)-AVERAGE(AK5:AP5)" align="right">398.08</td> <td class="xl26" x:num="" x:fmla="=AVERAGE(AS5:AX5)-AVERAGE(AQ5:AR5)" align="right">0.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">LZ214</td> <td x:num="" align="right">1</td> <td class="xl24" x:num="" align="right">4158.50</td> <td class="xl24" x:num="" align="right">2686.00</td> <td class="xl24" x:num="" align="right">2936.50</td> <td class="xl24" x:num="" align="right">2512.00</td> <td class="xl24" x:num="3196.857" align="right">3196.86</td> <td class="xl24" x:num="2424.5709999999999" align="right">2424.57</td> <td class="xl24" x:num="2292.8330000000001" align="right">2292.83</td> <td class="xl24" x:num="" align="right">2243.20</td> <td class="xl24" x:num="" align="right">4237.50</td> <td class="xl24" x:num="" align="right">2384.50</td> <td class="xl24" x:num="" align="right">3250.50</td> <td class="xl24" x:num="" align="right">2534.00</td> <td class="xl24" x:num="" align="right">3260.00</td> <td class="xl24" x:num="" align="right">2448.00</td> <td class="xl24" x:num="" align="right">2185.00</td> <td class="xl24" x:num="" align="right">2458.00</td> <td class="xl24" x:num="" align="right">0.50</td> <td class="xl24" x:num="" align="right">0.50</td> <td class="xl24" x:num="" align="right">0.75</td> <td class="xl24" x:num="" align="right">0.75</td> <td class="xl24" x:num="0.875" align="right">0.88</td> <td class="xl24" x:num="0.875" align="right">0.88</td> <td class="xl24" x:num="" align="right">0.75</td> <td class="xl24" x:num="0.625" align="right">0.63</td> <td class="xl24" x:num="" align="right">2801.00</td> <td class="xl24" x:num="2964.1669999999999" align="right">2964.17</td> <td class="xl24" x:num="2264.875" align="right">2264.88</td> <td class="xl24" x:num="" align="right">2593.75</td> <td class="xl24" x:num="2395.625" align="right">2395.63</td> <td class="xl24" x:num="2170.5709999999999" align="right">2170.57</td> <td class="xl24" x:num="1964.875" align="right">1964.88</td> <td class="xl24" x:num="1861.625" align="right">1861.63</td> <td class="xl24" x:num="" align="right">2326.50</td> <td class="xl24" x:num="" align="right">3026.50</td> <td class="xl24" x:num="" align="right">1893.50</td> <td class="xl24" x:num="" align="right">2487.50</td> <td class="xl24" x:num="" align="right">2318.50</td> <td class="xl24" x:num="" align="right">2172.00</td> <td class="xl24" x:num="" align="right">1806.50</td> <td class="xl24" x:num="" align="right">1944.50</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="" align="right">0.75</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="0.875" align="right">0.88</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl24" x:num="" align="right">1.00</td> <td class="xl26" x:num="" x:fmla="=K6-R6" align="right">1779.50</td> <td class="xl26" x:num="0.125" x:fmla="=Z6-S6" align="right">0.13</td> <td class="xl26" x:num="" x:fmla="=AI6-AP6" align="right">382.00</td> <td class="xl26" x:num="" x:fmla="=AX6-AQ6" align="right">0.00</td> <td class="xl26" x:num="" x:fmla="=AVERAGE(K6:L6)-AVERAGE(M6:R6)" align="right">621.75</td> <td class="xl26" x:num="0.27083333333333337" x:fmla="=AVERAGE(U6:Z6)-AVERAGE(S6:T6)" align="right">0.27</td> <td class="xl26" x:num="" x:fmla="=AVERAGE(AI6:AJ6)-AVERAGE(AK6:AP6)" align="right">572.75</td> <td class="xl26" x:num="0.10416666666666663" x:fmla="=AVERAGE(AS6:AX6)-AVERAGE(AQ6:AR6)" align="right">0.10</td> </tr> </tbody></table>
Thank you so much for reading through this tome! I'm happy to answer any other questions.

-Brady
 
Upvote 0
Confirm:
• Where the Diagnosis entry comes from.
• Each source workbook has one and only one worksheet
• The names of the Condition1 workbooks start with "6word"
• The names of the Condition2 workbooks start with "7word"
• In the Master workbook, on the Word sheet, Subject occupies column A, Diagnosis occupies column B.

Additionally, create a new folder somewhere and stick all the workbooks in there, or at least a copy of them to test. They can remain in the same folder they are in but for ease of testing, just copy them or drag them into some dedicated folder you create, example, C:\ConditionsSource and post what that new folder path is.

Any other details...I'm still fuzzy on why there are two sets of data that I would have thought are stacked in sets of 2 rows but that looks to not be the case given your results example.
 
Last edited:
Upvote 0
-Ignore the diagnosis for now--we have to put that in by hand
-Yes. So, for example, 6word_LZ297.xls has one and only one worksheet
-Yes.
-Yes.
-Yes. Subject occupies column A, Diagnosis B, then starts the avgs etc.

-Will do. Lets say for coding purposes, there's a folder called Test. Inside that folder is MasterList.xls. and one other folder, Data. Inside the Data folder are (copies of) ALL the source workbooks.

-If I understand your last point clearly, the data for each new subject is in it's own row. So, [subjnum],[Diag],[24 cells from 6word_subjnum.xls],[24 cells from 7word_subjnum.xls],[8 avgs based on the two conditions computed afterward--don't worry about these]

***The only other details I can think of are (ignore if irrelevant):
For the image condition:
-the MasterList worksheet tab is titled "Images"
-the files for Condition 1 all start with "6image"
-the files for Condition 2 all start with "7image"
-there are **48** items from EACH of the 2 workbooks (double from the "Words" condition) that need to be pulled into the MasterList "Images" worksheet.
-the corresponding cells in each workbook are:
(I've labeled the columns; the first data point (here 1762.67) is N7)
<table x:str="" style="border-collapse: collapse; width: 272pt;" width="362" border="0" cellpadding="0" cellspacing="0"><col style="width: 92pt;" width="122"> <col style="width: 93pt;" width="124"> <col style="width: 87pt;" width="116"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; width: 92pt;" width="122" height="17">N
</td> <td class="xl22" style="width: 93pt;" width="124">O
</td> <td class="xl22" style="width: 87pt;" width="116">P
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">AVG CONG RT 1</td> <td class="xl22">Median Cong RT 1</td> <td class="xl22">Prop Corr Cong 1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="1762.6666666666667" align="right" height="17">1762.67</td> <td class="xl22" x:num="" align="right">2085.00</td> <td class="xl22" x:num="" align="right">0.60</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">AVG INCONG RT 1</td> <td class="xl22">Median Incong RT 1</td> <td class="xl22">Prop Corr Incong 1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="" align="right" height="17">3135.00</td> <td class="xl22" x:num="" align="right">3135.00</td> <td class="xl22" x:num="0.33333333333333331" align="right">0.33</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">AVG CONG RT 2</td> <td class="xl22">Median Cong RT 2</td> <td class="xl22">Prop Corr Cong 2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="" align="right" height="17">2639.25</td> <td class="xl22" x:num="" align="right">2288.50</td> <td class="xl22" x:num="" align="right">0.80</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">AVG INCONG RT 2</td> <td class="xl22">Median Incong RT 2</td> <td class="xl22">Prop Corr Incong 2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="" align="right" height="17">9999.00</td> <td class="xl22" x:num="" align="right">9999.00</td> <td class="xl22" x:num="" align="right">0.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">AVG CONG RT 3</td> <td class="xl22">Median Cong RT 3</td> <td class="xl22">Prop Corr Cong 3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="" align="right" height="17">2036.00</td> <td class="xl22" x:num="" align="right">1759.00</td> <td class="xl22" x:num="" align="right">1.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">AVG INCONG RT 3</td> <td class="xl22">Median Incong RT 3</td> <td class="xl22">Prop Corr Incong 3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="" align="right" height="17">3452.00</td> <td class="xl22" x:num="" align="right">3452.00</td> <td class="xl22" x:num="" align="right">0.20</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">AVG CONG RT 4</td> <td class="xl22">Median Cong RT 4</td> <td class="xl22">Prop Corr Cong 4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="" align="right" height="17">1794.80</td> <td class="xl22" x:num="" align="right">1709.00</td> <td class="xl22" x:num="" align="right">1.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">AVG INCONG RT 4</td> <td class="xl22">Median Incong RT 4</td> <td class="xl22">Prop Corr Incong 4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="" align="right" height="17">2375.00</td> <td class="xl22" x:num="" align="right">2375.00</td> <td class="xl22" x:num="0.33333333333333331" align="right">0.33</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">AVG CONG RT 5</td> <td class="xl22">Median Cong RT 5</td> <td class="xl22">Prop Corr Cong 5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="1666.3333333333333" align="right" height="17">1666.33</td> <td class="xl22" x:num="" align="right">1550.00</td> <td class="xl22" x:num="" align="right">1.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">AVG INCONG RT 5</td> <td class="xl22">Median Incong RT 5</td> <td class="xl22">Prop Corr Incong 5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="" align="right" height="17">2600.00</td> <td class="xl22" x:num="" align="right">2600.00</td> <td class="xl22" x:num="" align="right">0.40</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">AVG CONG RT 6</td> <td class="xl22">Median Cong RT 6</td> <td class="xl22">Prop Corr Cong 6</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="" align="right" height="17">1509.00</td> <td class="xl22" x:num="" align="right">1550.00</td> <td class="xl22" x:num="" align="right">1.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">AVG INCONG RT 6</td> <td class="xl22">Median Incong RT 6</td> <td class="xl22">Prop Corr Incong 6</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="" align="right" height="17">3326.50</td> <td class="xl22" x:num="" align="right">2919.50</td> <td class="xl22" x:num="" align="right">0.80</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">AVG CONG RT 7</td> <td class="xl22">Median Cong RT 7</td> <td class="xl22">Prop Corr Cong 7</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="" align="right" height="17">1813.00</td> <td class="xl22" x:num="" align="right">1465.00</td> <td class="xl22" x:num="" align="right">1.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">AVG INCONG RT 7</td> <td class="xl22">Median Incong RT 7</td> <td class="xl22">Prop Corr Incong 7</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="2393.6666666666665" align="right" height="17">2393.67</td> <td class="xl22" x:num="" align="right">2432.00</td> <td class="xl22" x:num="" align="right">1.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">
</td> <td class="xl22">
</td> <td class="xl22">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">AVG CONG RT 8</td> <td class="xl22">Median Cong RT 8</td> <td class="xl22">Prop Corr Cong 8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="" align="right" height="17">1291.00</td> <td class="xl22" x:num="" align="right">1291.00</td> <td class="xl22" x:num="0.66666666666666663" align="right">0.67</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">AVG INCONG RT 8</td> <td class="xl22">Median Incong RT 8</td> <td class="xl22">Prop Corr Incong 8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" x:num="" align="right" height="17">2214.50</td> <td class="xl22" x:num="" align="right">2048.50</td> <td class="xl22" x:num="" align="right">0.80</td> </tr> </tbody></table>
so, a version of a formula to work out the needed cell numbers might be:

Code: 
     x="N"
     y=7
While y>74
     Grab Cell (x,y)
     y=y+8
     if x="N" and y=71; then
          y=9
     end
     if x="N" and y=73; then
          x="O"
          y=7
     end
     if x="O" and y=71; then
          y=9
     end
     if x="O" and y=73; then
          x="P"
           y=7
      end
     if x="P" and y=71; then
           y=9
      end
     if x="P" and y=73; then
           y=100
      end
End While
I'm sure there's a better way to do this, but this was my stab at it.

Hope this helps.
 
Upvote 0
This looks very close to what I am tyring to do. However, I am looking to pull all the data from one workbook based on a cell in a control sheet/tab.

I have not done any VBA stuff and my use of macros is strictly push the record button. I am willing to learn though.

Any help?

Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Macro to consolidate multiple worksheets into Master based on cell data?
Replies
1
Views
273
bobaol
B
N
  • Question
Count - specific consecutive order
Replies
6
Views
512
Peter_SSs
Peter_SSs
G
  • Solved
Change Macro to import files from hyperlinks
2
Replies
12
Views
427
GarnesGambit
G
A
  • Question
How can I use VBA to automate the process of importing data from multiple Excel files into a consolidated worksheet?
Replies
3
Views
529
Fluff
Fluff
I
  • Question
Chart Data Range - Changes Daily
Replies
9
Views
208
awoohaw
awoohaw

Forum statistics

Threads
1,214,979
Messages
6,122,551
Members
449,088
Latest member
davidcom

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top