Databases
StackOverflow2013
Postsdbo
POmacro extract data block
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmacro extract data block
    primarykey
    data
    text
    <p>I have worked on this problem for my entire day and can't solve it. </p> <p>The input data consists of several data blocks with the same number of rows and columns. Each data block has its name in the first line within the block. Besides, they are further separated by a blank row. </p> <pre><code>block1 name score value a 2 3 b 3 5 c 1 6 block2 name score value a 4 6 b 7 8 c 2 6 block3 name score value a 5 4 b 7 8 c 2 9 </code></pre> <p>The desired output is to extract the name and value column of each block, and then parallel them in columns. Like this:</p> <pre><code>value block1 block2 block3 a 3 6 4 b 5 8 8 c 6 6 9 </code></pre> <p>Thanks for your help!</p> <p><strong>UPDATE</strong> Thanks for your answer, Tony, and others! I just have another requirement. It is possible that some row in some tables are missing. In other words, as you mentioned previously, the row number may vary. Is it possible to fill in the corresponding cell in these tables with NA? i.e. the new input is like:</p> <pre><code>block1 name score value a 2 3 c 1 6 block2 name score value a 4 6 b 7 8 c 2 6 block3 name score value a 5 4 b 7 8 </code></pre> <p>The desired output now is like this:</p> <pre><code>value block1 block2 block3 a 3 6 4 b NA 8 8 c 6 6 NA </code></pre> <p><strong>UPDATE on Jul.3 (If it's inappropriate to make the question too long, I will move this part and make it a new question)</strong></p> <pre><code> block1 name score value a 2 3 b 3 5 c 1 6 block2 name score value a 4 6 b 7 8 c 2 6 block3 name score value a 5 4 b 7 8 c 2 9 </code></pre> <p>How can I pull both the value and its corresponding score and put them into one cell? Like this: The code indicates that the value is put into an dynamic array. Then the .range is assigned to this array. My first thought is to construct another array to store the value of the "score" column. Then loop through each element in both array, and concatenate them together. However, it seems that VBA does allow me to loop through the array, since its dimension is not defined. I tried REDIM, but it did not work. </p> <pre><code>value block1 block2 block3 a 3(2) 6(4) 4(5) b 5(3) 8(7) 8(7) c 6(1) 6(2) 9(2) </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJia
    1. USJia
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POmacro extract data block
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POmacro extract data block
      1. USJia
      1. VTFavorite
    1. COare the rows always the same? or do you need some sorting? Is this a one off job or do ypu need a macro that can be run on changing row/column counts?
      singulars
      1. POmacro extract data block
      1. USJohanness
    2. COI'm confused and made my code really messy. What I'm thinking is to extract the "value" column for all the tables, and rearrange them by dividing this column by the number of rows.
      singulars
      1. POmacro extract data block
      1. USJia
    3. COMy answer can be extended reasonably easily. However, does the sequence of names matter? If the names were in the sequence found in your example, they would be a-c-b. Do you really want Not applicable in the output; I would leave cell blank for missing values.
      singulars
      1. POmacro extract data block
      1. USTony Dallimore
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload