Combining data from multiple workbooks in a folder into one file

L

Lyndsey_11

New Member
Joined
Sep 27, 2017
Messages
3
Hello everyone,

I'm in desperate need some some VBA help!
I have a number of identical files saved in a folder which I basically need to combine into one report.

The format of each file is as follows -

A
B
C
D
E
F
G
H
1
Employee Name
2
Employee Grade
3
Employee Mark
4
Objective 1MarkWhatHow
5
Objective 2MarkWhatHow
6
Objective 3MarkWhatHow
7
Objective 4MarkWhatHow
8
Objective 5MarkWhatHow
9
Objective 6MarkWhatHow

<tbody>
</tbody>

I would like to extract the above information from each individual file in the folder, and combine into one file in the following format -

A
B
C
D
E
F
G
H
1
Employee Name (1)Employee Grade (1)Employee Mark (1)Objective 1MarkWhatHow
2
Employee Name (1)Employee Grade (1)Employee Mark (1)Objective 2MarkWhatHow
3
Employee Name (1)Employee Grade (1)Employee Mark (1)Objective 3MarkWhatHow
4
Employee Name (1)Employee Grade (1)Employee Mark (1)Objective 4MarkWhatHow
5
Employee Name (1)Employee Grade (1)Employee Mark (1)Objective 5MarkWhatHow
6
Employee Name (1)Employee Grade (1)Employee Mark (1)Objective 6MarkWhatHow
7
Employee Name (2)Employee Grade (2)Employee Mark (2)Objective 1MarkWhatHow
8
Employee Name (2)Employee Grade (2)Employee Mark (2)Objective 2MarkWhatHow
9
etc...

<tbody>
</tbody>

Hopefully that makes some sense! :confused:
Does anyone have some code that can do that please?

Many thanks in advance! :)
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
Is Mark a name, what is the difference between Mark (1) and Mark ?

Is each file just for one person ?

could you put each person data on different sheets in just one workbook ?
 
Upvote 0
I'm creating this file for work - it's combining each employees mid-year mark/ratings. Mark (1) is the employees overall score, and each 'Mark' is the score against each of their objectives - does that make sense?

There will be one file for each person yes (so a total of about 200 files)
I could put each person on a different tab, but we want to be able to filter and pivot - so this wouldn't really work I don't think
 
Upvote 0
bill
g1
Employee Mark6.5
Objective 16What How
Objective 27What How
Objective 36What How
Objective 47What How
Objective 56What How
Objective 67What How
fred
g2
Employee Mark8.5
Objective 18What How
Objective 29What How
Objective 38What How
Objective 49What How
Objective 58What How
Objective 69What How
namegradeMARKobj1score1what1how1obj2score2what2how2obj3score3what3how3
billg16.5ert6fghjdfghtyui7zxcxcvlkjh6fghjkfghjkletc
fredg28.5dfg8dfgfghfghh9sdfdfgfgh8xcvbmnbv
is this anything near what you want - would be easy to pivot and sort in this format

<colgroup><col span="3"><col><col span="13"></colgroup><tbody>
</tbody>
 
Upvote 0
bill
g1
MARK6.5Sum = 3
Objective 16WhatasdHowzxc myrow = 30
Objective 27WhatsdfHowxcv For j = 1 To 10 Step 9
Objective 36WhatdfgHowcvb x = j - 1
Objective 47WhatfghHowvbn Cells(myrow, 1) = Cells(1 + x, 1)
Objective 56WhatghjHowbnm Cells(myrow, 2) = Cells(2 + x, 1)
Objective 67WhatjklHowzvm Cells(myrow, 3) = Cells(3 + x, 2)
fred For z = 4 + x To 9 + x
g2 For zz = 2 To 6 Step 2
MARK8.5 Sum = Sum + 1
Objective 18WhatpoiHowlkj Cells(myrow, Sum) = Cells(z, zz)
Objective 29WhatoiuHowkjh Next zz
Objective 38WhatiuyHowjhg Next z
Objective 49WhatuytHowhgf Sum = 3
Objective 58WhatytrHowgfd myrow = myrow + 1
Objective 69WhattreHowfds Next j
100 End Sub
the above macro strips the data from the top table
GRADEMARKSCORE1OBJ1HOW1SCORE2OBJ2HOW2SCORE3OBJ3HOW3SCORE4OBJ4HOW4SCORE5OBJ5HOW5SCORE6OBJ6HOW6
billg16.56asdzxc7sdfxcv6dfgcvb7fghvbn6ghjbnm7jklzvm
fredg28.58poilkj9oiukjh8iuyjhg9uythgf8ytrgfd9trefds

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Need to identify specific data from a big list to determine incentive eligibility for a retail.
Replies
3
Views
170
kevin9999
K
C
  • Question
Pivot Table from Power Query - reorder Columns (Excel 2021)
Replies
4
Views
322
craigcurtin
C
U
  • Question
VBA, Formula, or Data Validation Excel form
Replies
1
Views
382
NateSC
NateSC
R
  • Question
Formula for % of employee trained based on certain criteria
Replies
5
Views
259
Armstrong_N14
A
I
  • Question
Tracking vacation requests
Replies
9
Views
535
KRice
K

Forum statistics

Threads
1,216,077
Messages
6,128,673
Members
449,463
Latest member
Jojomen56

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