PDA

View Full Version : Copy Paste from multiple worksheets into one based on multiple criteria



Beatrix
03-11-2014, 11:47 AM
Hi All ,

I need to copy/paste data from x-y-z-p to master tab based on unique id and headings. There are 32 headings in overall and each tab has different headings except demografic data. I gave a number to each heading and designed a master tab by including unique ID. I can't use Vlookup to do matching as it's not only matching it's putting all data in one place but some worksheets are repeating same person with different data( kind of case summary history)

I need to run data from individual tabs to master tab by using unique ID and heading number in each tab. Master tab has the unique ID for each row which is number of records (not unique records)

I attached a sample workbook.

Can ayone help me on this please?

Cheers
B.

mancubus
03-11-2014, 03:28 PM
hi. i think this thread is successor to previous one.

try this.

since all worksheets have unique id's you dont have to consolidate them in master ws before.



Sub cons_wss()


Dim wsM As Worksheet
Dim LastRow As Long, j As Long, ColNumMaster As Long, LRMaster As Long

Set wsM = Worksheets("Master")
wsM.Range("A2:AG" & Rows.Count).Clear
'clear previous data, if any

For Each e In Array("x", "y", "z", "p")
LRMaster = wsM.Cells(Rows.Count, "A").End(xlUp).Offset(1).Row
With Worksheets(e)
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For j = 1 To .Cells(1, Columns.Count).End(xlToLeft).Column
On Error Resume Next 'in case column header does not exist in Master
ColNumMaster = Application.Match(.Cells(1, j), wsM.Rows(1), 0)
On Error GoTo 0
If ColNumMaster > 0 Then 'header is found in Master and ColNumMaster is its column number
.Range(.Cells(2, j), .Cells(LastRow, j)).Copy wsM.Cells(LRMaster, ColNumMaster)
End If
Next
End With
Next


End Sub

mancubus
03-12-2014, 05:45 AM
noticing the values in Column A of master ws is inserted by the code in your previous thread, you may want to keep these values.

should this be the case, you may want to use a code which excludes unique id's. so i amended the previous code.

but you must be very careful. the order of worksheet names must be the same for both macros: For Each e In Array("x", "y", "z", "p") x, then y, then z, then p. the rows (= 'records') in these 4 worksheets must not be changed.

running two macros one after another is of crucial importance. if possible, creating a single macro will be better. (you can just copy the second macro after first one.)

i assumed col A in four worksheets are out of consolidation scope, and so is row 1 (header row).


Sub cons_wss_2()

Dim wsM As Worksheet
Dim LastRow As Long, j As Long, ColNumMaster As Long, LRMaster As Long

Set wsM = Worksheets("Master")

LRMaster = 2
For Each e In Array("x", "y", "z", "p")
With Worksheets(e)
LastRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
For j = 2 To .Cells(1, Columns.Count).End(xlToLeft).Column
On Error Resume Next 'in case column header does not exist in Master
ColNumMaster = Application.Match(.Cells(1, j), wsM.Rows(1), 0)
On Error GoTo 0
If ColNumMaster > 0 Then 'header is found in Master and ColNumMaster is its column number
.Range(.Cells(2, j), .Cells(LastRow, j)).Copy wsM.Cells(LRMaster, ColNumMaster)
End If
Next
End With
LRMaster = LRMaster + LastRow - 1 '-1 is for header rows
Next
End Sub

Beatrix
03-12-2014, 08:23 AM
Hi mancubus ,

Thanks very much for your reply. I am trying to create unique IDs for 4 different databases and put everything in one place. This below code creates Unique ID. Kris helped me on this and it works great. However I need to combine everything by pulling data from individual tabs. The code you sent me copy/paste data but it doesn't combine everything. For instance a_26097_527F0 is in tab x also in tab y so I need to pull data from y for the headings 9,19,20,21,22,23,24,25,26,27. The code you send me doesn't fill in the all headings in master if the unique id repeats in other tabs for other headings. Also you are right. I think I don't need to consolidate UID with master as I already have it in individual tabs. Now my question is can you help me to modify below code to create the unique ID s in column A in each individual tab instead of master ? as I did copy/paste the UID from master to each individual which is not an effective solution. I designed the structure slightly wrong.

First it creates the unique id in column A in each tab then merging your script with this one to combine data in master tab?

If you noticed some headings are repeating in each tab which are demographic data and some headings are specific for only 1 tab. For instance, o_38019_45E12 is in tab z and tab p so data should be pulled from each field in both tab and master tab should be filled for each heading where ever the Unique ID repeats.

Did I make it complicated? This task is complicated anyway. Data quality is very poor in each database unfortunately.



Sub kTest()

Dim k, e, v, ka(), n As Long
Dim fso As Object, i As Long, u As String

Set fso = CreateObject("scripting.filesystemobject")

Redim ka(1 To 10000, 1 To 1) '<<< replace 10000 with possible max records

With CreateObject("scripting.dictionary")
.comparemode = 1
For Each e In Array("x", "y", "z", "p")
k = Worksheets(e).Range("a1").CurrentRegion.Resize(, 4).Value2
For i = 2 To UBound(k, 1)
If Len(k(i, 3)) * Len(k(i, 4)) Then
If Not .exists(k(i, 3) & "|" & k(i, 4)) Then
n = n + 1
u = Replace(Replace(fso.gettempname, "rad", vbNullString), ".tmp", vbNullString)
u = k(i, 3) & "_" & k(i, 4) & "_" & u
.Item(k(i, 3) & "|" & k(i, 4)) = u
ka(n, 1) = u
Else
n = n + 1
ka(n, 1) = .Item(k(i, 3) & "|" & k(i, 4))
End If
End If
Next
Next
End With
If n Then
With Worksheets("master")
.Range("a" & .Rows.Count).End(3).Offset(1).Resize(n) = ka
End With
End If

End Sub

Beatrix
03-12-2014, 08:38 AM
Also I need to have all headings in master from 0-32 including UID.

Cheers
B.

mancubus
03-12-2014, 08:48 AM
you are welcome. i totally misunderstood the requirement i guess. :) you want to fill in the columns in master ws from 4 sheets. let me work on it. i think i will have questions to understand the whole.

mancubus
03-12-2014, 12:15 PM
hi Beatrix.

can you please upload a file which demonstrates the after macro structure of Master sheet. you can manually copy paste from other 4 tabs. also using different font colors for these tabs will help me understand the requirement more clearly.

Beatrix
03-13-2014, 11:20 AM
Hi mancubus

I am creating a data mapping with colour coding to make the requriment much clearer. It's getting more complicated to me so it's difficult to explain it but this data map should work. I'll send it to you as soon as it's finished.

thanks again.
B.

Beatrix
03-17-2014, 12:24 PM
Hi mancubus.

I created before/after files. Attached one is after. I'll also send "before". File size limit didn't allow me to upload both and couldn't zip them.

Cheers
B.

Beatrix
03-17-2014, 12:27 PM
here is "before" file.

thanks again.

B.

mancubus
03-17-2014, 02:12 PM
Hi Beatrix.

i examined both files. the "before" file is the same file in post #1, ok. and the "after" file is the same as what i assumed before seeing it.

terminology:
report = worksheet
field = column
record = row

you have 4 reports (worksheets). each report has the same or different fields (columns). in the end, you want to set up a new report (or data base or master worksheet) by completing columns 1-32 from 4 reports.
some fields exist in more than 1 report. but their values are the same. for example fields 5 and 6 which are "full name" and "DOB" fields. and they are in columns C and D in all reports.
one person may have more than 1 record (row).
each report may not have all persons data.

if a person's data exist only in one report, it's not difficult to consolidate. copy related fields data to first blank row under the same header in master report.
but what if a person's data exist in more than one report.

let's take first person as an example from report x, which is a_26097_527F0.
x: 8 records, 15 fields: 1, 4, 5, 6, 7, 8, 10, 11, 12, 13, 14, 15, 16, 17, 18
y: 1 record, 16 fields: 1, 4, 5, 6, 7, 19, 20, 8, 10, 21, 22, 23, 24, 25, 26, 27
z: no record
p: 2 records, 10 fields: 3, 4, 5, 6, 7, 8, 9, 30, 31, 32

master in "after" file shows;

fields 1, 4, 5, 6, 7, 8, 10, 11, 12, 13, 14, 15, 16, 17, 18 are from x

fields 19, 20, 21, 22, 23, 24, 25, 26, 27 are from y.
in y there is only 1 a_26097_527F0. fields 19 and 20 are copied down to match all 8 records from x but fields 21, 22, 23, 24, 25, 26, 27 are copied only once in the first row. is it the desired result or you just copied once?

fields 3, 9, 30, 31, 32 are from p.
in p there are 2 a_26097_527F0. fields 3, 8 and 32 are copied down to match all 8 records from x but fields 30 and 31 are copied only once in the first and second rows. again is it the desired result or you just copied once?


example file shows report (or worksheet) x has the most number of records for the same person. what if, say, p had 12 records for a_26097_527F0?



cheers.



ps: you dont need _527F0 bit for UID in this structure. it's the same for all records of the same person. your UID comes from full name and BOD. so why not use only these two values.

mancubus
03-18-2014, 05:58 AM
ok. i missed the row 3 explanation, ie, "same" or "different". you copied only once because in other rows, if any, values are all different. ----- yet again offering a solution seems a way above my head. ----- btw, the worst i could do will be consolidating all rows from worksheets in master.

Beatrix
03-19-2014, 08:51 AM
Thanks for all analysis. Unfortunately the same person's data might exist more than one report. Yes this one is complicated and I've been trying to find a structure by using numbers and "same" "different" categories and column C and column D where Surname and DOB are. You are right in this structure no need UID.

ok what about to copy/paste all "different fields" from each worksheet to "master" first then fill in the rest of headings in master by using heading numbers and column C and D?

In that case ;

x: 1, 4, 5, 6, 7, 8, 10, 11, 12, 13, 14, 15, 16, 17, 18 red ones are in worksheet x only.

y: 1, 4, 5, 6, 7, 19, 20, 8, 10, 21, 22, 23, 24, 25, 26, 27 red ones are in worksheet y only.

z: 2, 4, 5, 6, 28, 29 red ones are in worksheet z only.

p: 3, 4, 5, 6, 7, 8, 9, 30, 31, 32 red ones are in worksheet p only.

So the numbers in red goes(just copy paste) to master worksheet with 4(first name) 5 (surname) 6 (DOB) then 1-2-3-7-8-9-10 would be pulled from individual worksheets based on criteria Surname and DOB?

mancubus
03-19-2014, 12:08 PM
btw, the worst i could do will be consolidating all rows from worksheets in master.

this is what i mentioned.

it just consolidates the rows from all wss one after another, changes font colors based on ws name, adds an extra column to show which ws that row comes from.

this requires a lot of manual work, unfortunately. missing column values must be manually copied and the rows whose values are copied must be deleted.

i commented out the related 2 lines to create UID from full name and DOB only. if you like using GetTempName, pls uncomment these lines.

in order to avoid guessing the total number of rows from all 4 wss, i used a one-dimensional array and resized it as needed. ie, instead of ka(1 To 10000, 1 To 1) i used ka(1 To x), x being a variable for the increasing UBound of the array.



(btw, i'll keep working on a real solution.)



Sub Create_Unique_ID_Based_Two_Cols_n_Cons_Wss_v2()'http://www.vbaexpress.com/forum/showthread.php?49149-Copy-Paste-from-multiple-worksheets-into-one-based-on-multiple-criteria


Dim fso As Object, wsM As Worksheet
Dim k, e, ka(), u As String
Dim n As Long, i As Long, x As Long
Dim calc As Long, LastRow As Long, j As Long
Dim ColNumMaster As Long, LRMaster As Long, LCMaster As Long

With Application
.DisplayAlerts = False
.ScreenUpdating = False
.EnableEvents = False
calc = .Calculation
.Calculation = xlCalculationManual
End With

x = 0
Set fso = CreateObject("Scripting.FileSystemObject")
With CreateObject("Scripting.Dictionary")
.CompareMode = 1
For Each e In Array("x", "y", "z", "p")
k = Worksheets(e).Range("A1").CurrentRegion.Resize(, 4).Value2
x = x + UBound(k, 1) - 1 'to be able to resize the array
ReDim Preserve ka(1 To x)
Worksheets(e).Columns(1).Insert
Worksheets(e).Cells(1) = Worksheets("data fields").Range("B2")
For i = 2 To UBound(k, 1)
If Len(k(i, 3)) * Len(k(i, 4)) Then
If Not .Exists(k(i, 3) & "|" & k(i, 4)) Then
n = n + 1
'u = Replace(Replace(fso.GetTempName, "rad", vbNullString), ".tmp", vbNullString)
u = k(i, 3) & "_" & k(i, 4) '& "_" & u
.Item(k(i, 3) & "|" & k(i, 4)) = u
ka(n) = u
Worksheets(e).Range("A" & i) = ka(n)
Else
n = n + 1
ka(n) = .Item(k(i, 3) & "|" & k(i, 4))
Worksheets(e).Range("A" & i) = ka(n)
End If
End If
Next
Worksheets(e).Columns.AutoFit
Next
End With

On Error Resume Next
Set wsM = Worksheets("master")
If wsM Is Nothing Then
Set wsM = Worksheets.Add(after:=Worksheets(Worksheets.Count))
wsM.Name = "master"
Else
wsM.Cells.Clear
End If
On Error GoTo 0

With Worksheets("data fields")
df = .Range("B2:B" & .Range("B" & .Rows.Count).End(xlUp).Row).Value2
End With

With wsM
.Cells(1).Resize(, UBound(df)) = Application.Transpose(df)
LCMaster = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
End With

LRMaster = 2

For Each e In Array("x", "y", "z", "p")
With Worksheets(e)
LastRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
For j = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column
On Error Resume Next 'in case column header does not exist in Master
ColNumMaster = Application.Match(.Cells(1, j), Application.Transpose(df), 0)
On Error GoTo 0
If ColNumMaster > 0 Then 'header is found in Master and ColNumMaster is its column number
.Range(.Cells(2, j), .Cells(LastRow, j)).Copy wsM.Cells(LRMaster, ColNumMaster)
wsM.Cells(LRMaster, LCMaster).Resize(LastRow - 1) = e
End If
Next
End With
LRMaster = LRMaster + LastRow - 1 '-1 is for header rows
Next

With wsM
.Cells(1, .Columns.Count).End(xlToLeft).Offset(, 1) = "Worksheet_Name"
.Cells(1).Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlYes 'sort master on UID's
.Columns.AutoFit
With .Cells(1).CurrentRegion
For i = 2 To .Rows.Count
.Rows(i).Font.ColorIndex = Choose(Application.Match(.Range("AH" & i), Array("x", "y", "z", "p"), 0), 3, 4, 5, 12)
Next
End With
End With

With Application
.DisplayAlerts = True
.ScreenUpdating = True
.EnableEvents = True
.Calculation = calc
End With

End Sub

mancubus
03-19-2014, 03:02 PM
i hope below code works for you.

the worksheet structure must be like attached file.

data fields worksheet must not be deleted.
column headings must be in column B. (from B2 then down.)
B2 value must be "UID".

(otherwise you should modify the code according to changes you will make!)

i added another macro to revert the spreadsheet back to before macro situation. stg like "undo". :)


cheers.



Sub Create_Unique_ID_Based_Two_Cols_n_Cons_Wss_Final()
'http://www.vbaexpress.com/forum/showthread.php?49149-Copy-Paste-from-multiple-worksheets-into-one-based-on-multiple-criteria


Dim fso As Object, wsM As Worksheet, wsU As Worksheet
Dim k, e, ka()
Dim u As String
Dim n As Long, i As Long, x As Long, j As Long
Dim calc As Long, LastRow As Long, m As Long
Dim ColNumMaster As Long, FRuid As Long, LRuid As Long

With Application
.DisplayAlerts = False
.ScreenUpdating = False
.EnableEvents = False
calc = .Calculation
.Calculation = xlCalculationManual
End With

On Error Resume Next
Set wsM = Worksheets("master")
If wsM Is Nothing Then
Set wsM = Worksheets.Add(after:=Worksheets(Worksheets.Count))
wsM.Name = "master"
Else
wsM.Cells.Clear
End If
On Error GoTo 0

With Worksheets("data fields")
df = .Range("B2:B" & .Range("B" & .Rows.Count).End(xlUp).Row).Value2
End With

With wsM
.Cells(1).Resize(, UBound(df)) = Application.Transpose(df)
End With

On Error Resume Next
Set wsU = Worksheets("uid")
If wsU Is Nothing Then
Set wsU = Worksheets.Add(after:=Worksheets(Worksheets.Count))
wsU.Name = "uid"
Else
wsU.Cells.Clear
End If
On Error GoTo 0

With wsU
.Range("A1:E1") = Array("UID", "x", "y", "z", "p")
End With

x = 0
Set fso = CreateObject("Scripting.FileSystemObject")
With CreateObject("Scripting.Dictionary")
.CompareMode = 1
For Each e In Array("x", "y", "z", "p")
k = Worksheets(e).Range("A1").CurrentRegion.Resize(, 4).Value2
x = x + UBound(k, 1) - 1
ReDim Preserve ka(1 To x)
Worksheets(e).Columns(1).Insert
Worksheets(e).Cells(1) = Worksheets("data fields").Range("B2")
For i = 2 To UBound(k, 1)
If Len(k(i, 3)) * Len(k(i, 4)) Then
If Not .Exists(k(i, 3) & "|" & k(i, 4)) Then
n = n + 1
u = k(i, 3) & "_" & k(i, 4)
.Item(k(i, 3) & "|" & k(i, 4)) = u
ka(n) = u
Worksheets(e).Range("A" & i) = ka(n)
wsU.Range("A" & Rows.Count).End(xlUp).Offset(1) = ka(n)
Else
n = n + 1
ka(n) = .Item(k(i, 3) & "|" & k(i, 4))
Worksheets(e).Range("A" & i) = ka(n)
wsU.Range("A" & Rows.Count).End(xlUp).Offset(1) = ka(n)
End If
End If
Next i
Worksheets(e).Columns.AutoFit
Worksheets(e).Cells(1).Sort Key1:=Worksheets(e).Cells(1), Order1:=xlAscending, Header:=xlYes 'sort ws on UID's
Next e
End With

With wsU
'determine the number of same uid in all wss
.Cells(1).Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlYes 'sort ws on UID's
.Cells(1).CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlYes
For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
For j = 2 To 5
.Cells(i, j) = Application.CountIf(Worksheets(.Cells(1, j).Value).Columns(1), .Cells(i, 1))
Next j
wsM.Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(Application.Max(.Range("B" & i & ":E" & i))) = .Cells(i, 1)
'write the uid's to master max number of wss times
Next i
End With

For Each e In Array("x", "y", "z", "p")
With Worksheets(e)
LastRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
For i = 2 To LastRow
FRuid = wsM.Columns(1).Find(.Cells(i, 1), , , , xlByRows, xlNext).Row 'first row of uid in master
LRuid = wsM.Columns(1).Find(.Cells(i, 1), , , , xlByRows, xlPrevious).Row 'first row of uid in master
For j = 1 To .Cells(1, .Columns.Count).End(xlToLeft).Column
On Error Resume Next 'in case column header does not exist in Master
ColNumMaster = Application.Match(.Cells(1, j), wsM.Rows(1), 0)
On Error GoTo 0
If ColNumMaster > 0 Then 'header is found in Master and ColNumMaster is its column number
Select Case ColNumMaster
Case 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 18, 19, 20, 21, 30, 33
'fields: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 17, 18, 19, 20, 29, 32
For m = FRuid To LRuid
If wsM.Cells(m, ColNumMaster) = "" Then
wsM.Cells(m, ColNumMaster) = .Cells(i, j)
End If
Next m
Case Else
'fields: 11, 12, 13, 14, 15, 16, 21, 22, 23, 24, 25, 26, 27, 28, 30, 31
For m = FRuid To LRuid
If wsM.Cells(m, ColNumMaster) = "" Then
wsM.Cells(m, ColNumMaster) = .Cells(i, j)
Exit For
End If
Next m
End Select
End If
Next j
Next i
End With
Next e

With Application
.DisplayAlerts = True
.ScreenUpdating = True
.EnableEvents = True
.Calculation = calc
End With

End Sub

Beatrix
03-20-2014, 05:33 AM
That's perfect mancubus!!:thumb

I will run it on real data to test the outcome. Thanks very much!!!

Cheers
B.

mancubus
03-20-2014, 06:30 AM
you are welcome. remember Case 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 18, 19, 20, 21, 30, 33 condition is for same for all same UID values. and Case Else is for different for same UID values as stated in row 3 of after.xlsx.

Beatrix
04-08-2014, 03:42 AM
you are welcome. remember Case 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 18, 19, 20, 21, 30, 33 condition is for same for all same UID values. and Case Else is for different for same UID values as stated in row 3 of after.xlsx.

Hi Mancubus ,

Unfortunately this didn't work :( I merged 21.165 row from 4 different data set and for most of the records UID and surname&DOB didn't match therefore it mixed 2 different people data in one row. Is it possible to copy paste all data from tab x into master for column 1,4,5,6,7,8,10,11,12,13,14,15,16,17,18 first then go to tab y and check for the UID if matches with any UID in master tab if it does then fill in 19,20,21,22,23,24,25,26,27 only. For those which don't match then copy paste from y to master tab at the bottom of the list including all headings from y which are 1,4,5,6,7,8,10,19,20,21,22,23,24,25,26,27 then go to z and check if the same UID is in master tab then fill in the 28 29 only if the UID doesn't match then copy paste everything from z to master by including 2,4,5,6,28,29 then go to the last tab which is p and check if any cases match in master tab if it does then fill in 9,30,31,32 only if it doesn't copy paste everything which are 3,4,5,6,7,8,9,30,31,32 from p to master at the bottom of the list.

Also
Case 1,2,3,4,5,6,7,8,9,10,17,18,19,20,29,32 same and 11,12,13,14,15,16,21,22,23,24,25,26,27,28,30,31 should be different

This one is the most complicated I have ever seen :eek:

mancubus
04-08-2014, 04:28 AM
hi.

it should be doing that actually. :)

but not in a way as you described in your last post.

the code inserts a blank column before Column A to all related sheets in question for the UID's created from full name and DOB. therefore, column numbers are incremented by 1 and we use Case 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 18, 19, 20, 21, 30, 33 instead of Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 17, 18, 19, 20, 29,32.

mancubus
04-08-2014, 04:35 AM
i'll try to comment what the code is doing, and maybe this way we can find what is wrong.

mancubus
04-08-2014, 05:39 AM
first bit adds two worksheets "master" and "uid", if they dont exist; clears their existing data if they exist. (master is for consolidation and uid is for controlling the number of same persons's records in all 4 sheets (x, y, z, p))
writes column headers from worksheet "data fields" to worksheet "master".
adds headers ("UID", "x", "y", "z", "p") to worksheet "uid".


On Error Resume Next
Set wsM = Worksheets("master")
...
...
...
.Range("A1:E1") = Array("UID", "x", "y", "z", "p")
End With



second bit creates an id by concatenating cell values in columns C and D (format: FirstName MiddleName LastName_BOD). inserts a blank column as their first column, writes these id's in the corresponding cells of column A of all 4 sheets (x, y, z, p). writes these id's to column A of worksheet "uid" as well. then sorts worksheets on uid's, ie Column A.


With CreateObject("Scripting.Dictionary")
.CompareMode = 1
...
...
...
Next e
End With


the third bit counts the number of records of the same person in all 4 worksheets. this is necessary for covering the all records of the same person. and writes the sorted uid's for the same person maximum (of 4 ws) number of times) in col A of master.


With wsU
'determine the number of same uid in all wss
...
...
...
End With


now we have 6 worksheets having uid's in their column A; with one difference, worksheet "uid" has unique list of uid's.

the last bit finally consolidates the data from 4 sheets (x, y, z, p) in master.


For Each e In Array("x", "y", "z", "p")
With Worksheets(e)
...
...
...
End With
Next e


remember uid's in worksheet uid are sorted (so are in master)?

loops row 2 to LastRow in 4 wss.
looks for uid in col A of "master" up-down, finds the first occurence of uid, returns its row number as FRuid.
looks for uid in col A of "master" bottom-up, finds the first (last, from up-down perspective) occurence of uid, returns its row number as LRuid.
loops column 2 to LastCol (.Cells(1, .Columns.Count).End(xlToLeft).Column) in 4 wss.
looks for headers in Row 1 of "master" left-right, finds the header, returns its column number as ColNumMaster.
writes the cell value (.Cells(i, j)) from 4 worksheets to master, in the intersect cell (FRuid To LRuid, ColNumMaster), if that cell is not previously inserted a value, according to outer and inner loops.