Copy data from day table to database.

New Contributor

Hi,

 

The task that I want to execute is basically copying the data from master table to database, based on date and multiple id’s. So every day users 1,2,3…… execute tasks A,B,C at the end of the day I want to transfer that data into database based on date user and task that they did. Database is sorted Date - rows, User - columns, Tasks - sub columns.

 

Thank you for your responses.

3 Replies

@Tautvydas 

=IFERROR(INDEX($B$4:$B$19,MATCH(1,($C$4:$C$19=$F4)*($A$4:$A$19=G$3),0)),"")

You can try INDEX and MATCH. The ranges can be adapted as required. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

user task.JPG

 

@Quadruple_Pawn 

 

In my specific case I look at multiple data points INDEX and MATCH can only return single point similar like VLOOKUP function. I'm looking for solution like in screenshot below. Where the data is being copied automatically from Main table to long term database.   

Tautvydas_0-1661194430797.png

 

@Tautvydas 

Sub database()

Dim i As Long
Dim j As Long

j = Range("H" & Rows.Count).End(xlUp).Row
i = Application.Match(Cells(2, 3), Range(Cells(4, 8), Cells(j, 8)), 0) + 3

Range("B4:D4").Copy Destination:=Range("I" & i, "K" & i)
Range("B5:D5").Copy Destination:=Range("L" & i, "N" & i)
Range("B6:D6").Copy Destination:=Range("O" & i, "Q" & i)

End Sub

Maybe this code is helpful. In the attached file you can click the button in cell G2 to run the macro. Then you can enter a new date and new data in the main table and run the macro again.