Transfer data from one table to another

[Hambone70]

Hello!

We have an annual work deconflictor in which we input the time off for our employees, divided into AM and PM (attached workbook 2016 Deconflictor).

We need to transfer the same data into our weekly schedule (attached workbook 2016 Weekly Schedule) with the employee's name and his/her reason off listed as per the weekly schedule.

I have manually transferred the data into the weekly schedule for 1-3 Jan to demonstrate what we need.

Thanking you in anticipation!

[JohnTopley]

In A4 of weekly schedule

=IFERROR(INDEX('[2016-deconflictor.xlsm]Deconflictor'!$A$3:$A$12,SMALL(IF('[2016-deconflictor.xlsm]Deconflictor'!$O$3:$O$12<>"",ROW('[2016-deconflictor.xlsm]Deconflictor'!$A$3:$A$12)-ROW('[2016-deconflictor.xlsm]Deconflictor'!$A$3)+1,""),ROWS($A$3:A3)),MATCH($A$1,'[2016-deconflictor.xlsm]Deconflictor'!$O$1:$S$1,0)),"")

in B4

=IFERROR(INDEX('[2016-deconflictor.xlsm]Deconflictor'!$O$3:$O$12,SMALL(IF('[2016-deconflictor.xlsm]Deconflictor'!$O$3:$O$12<>"",ROW('[2016-deconflictor.xlsm]Deconflictor'!$A$3:$A$12)-ROW('[2016-deconflictor.xlsm]Deconflictor'!$A$3)+1,""),ROWS($A$3:B3)),MATCH($A$1,'[2016-deconflictor.xlsm]Deconflictor'!$O$1:$S$1,0)),"")

in C4

=IFERROR(INDEX('[2016-deconflictor.xlsm]Deconflictor'!$A$3:$A$12,SMALL(IF('[2016-deconflictor.xlsm]Deconflictor'!$P$3:$P$12<>"",ROW('[2016-deconflictor.xlsm]Deconflictor'!$A$3:$A$12)-ROW('[2016-deconflictor.xlsm]Deconflictor'!$A$3)+1,""),ROWS($A$3:C3)),MATCH($A$1,'[2016-deconflictor.xlsm]Deconflictor'!$O$1:$S$1,0)),"")

in D4

=IFERROR(INDEX('[2016-deconflictor.xlsm]Deconflictor'!$P$3:$P$12,SMALL(IF('[2016-deconflictor.xlsm]Deconflictor'!$P$3:$P$12<>"",ROW('[2016-deconflictor.xlsm]Deconflictor'!$A$3:$A$12)-ROW('[2016-deconflictor.xlsm]Deconflictor'!$A$3)+1,""),ROWS($A$3:D3)),MATCH($A$1,'[2016-deconflictor.xlsm]Deconflictor'!$O$1:$S$1,0)),"")

Enter all with Ctrl+Shift+Enter


These formulae cannot simply copied and pasted to the other columns because of way the data is arranged (been here before several times!!!)

[Hambone70]

Thanks, John! That's perfect.

I shall endeavor to dissect your solution to learn how it works. I need to apply the same technique to the entire year's schedule with a daily variable list of employees.

Thanks again!

[Miskondukt]

JT, If I may, what is this type of array formula called or where can I find some info on how to piece this string together rather than making another post with almost the same question. I want to figure this out once and for all!

Thank ya

[Hambone70]

I need to make a modification. If an employee has "Inactive" as a reason for absence, I don't want their name listed in the weekly schedule "Off" section. For any other reason, I do want the employee's name and reason, just as it works now.

Is this an easy modification?

I, too, would like to learn more about advanced Excel formula techniques. Any reference materials you can recommend?

Thanks!

[Hambone70]

Oops... double post.

[JohnTopley]

=IFERROR(INDEX('[2016-deconflictor.xlsm]Deconflictor'!$A$3:$A$12,SMALL(IF(('[2016-deconflictor.xlsm]Deconflictor'!$O$3:$O$12<>"")*('[2016-deconflictor.xlsm]Deconflictor'!$O$3:$O$12<>"Inactive"),ROW('[2016-deconflictor.xlsm]Deconflictor'!$A$3:$A$12)-ROW('[2016-deconflictor.xlsm]Deconflictor'!$A$3)+1,""),ROWS($A$3:A3)),MATCH($A$1,'[2016-deconflictor.xlsm]Deconflictor'!$O$1:$S$1,0)),"")

Untested !

You need to put this change in the other formulae.

As per previous conversations about these workbooks I still maintain that re-designing the format of the data is preferable to the "hardcoding" required to change the formulae (since you cannot simply easily repeat them by dragging down a column or across a row).

[JohnTopley]

Re Excel Formula techniques: I haven't used any books on Excel (except one on VBA programming). My knowledge has been acquired through practice and looking at responses by the experts on this forum (these fora). I am never going to achieve "expert" status!!!

Search the Internet and I am sure you will find good references

Re the formula I used: this a "common" technique for extracting data based on varying criteria - again searching the web for INDEX/MATCH/SMALL should get you examples and explanations.

[Hambone70]

Hi John

Thanks for the info. I'll tap the internet for some examples and explanations.

I managed to create a weekly schedule from your formulae. In the attached workbooks, I used named ranges to simply the process. As per your earlier recommendation, there are no merged cells. I haven't incorporated the "Inactive" function yet.

Is there a technique I can use to extract the data from the deconflictor into the appropriate weekly schedule worksheets in the weekly schedule workbook? Or would I be better off extracting the entire year to another worksheet, then using HLOOKUP to pass the data to the weekly schedule? Also, is there a one-formula drag-across technique that would be less tedious?

Thanks again for your help!

[JohnTopley]

Take a look at these. I have changed the Conflictor design which allows you to copy and paste the formulas. Select a block of 4 (Name/AM/Name/PM) and copy the paste to the next date.

AM name

=IFERROR(INDEX([Deconflictor1.xlsx]Sheet1!$B$1:$U$1,SMALL(IF(([Deconflictor1.xlsx]Sheet1!$B$3:$U$365<>"")*(MOD(COLUMN([Deconflictor1.xlsx]Sheet1!$B$1:$U$1),2)=0)*([Deconflictor1.xlsx]Sheet1!$A$3:$A$365=A$2),COLUMN([Deconflictor1.xlsx]Sheet1!$B$1:$U$1)-COLUMN([Deconflictor1.xlsx]Sheet1!$B$1)+1,""),ROW($A1:A1))),"")

AM Reasson

=IFERROR(INDEX([Deconflictor1.xlsx]Sheet1!$B$3:$T$365,MATCH(A$2,[Deconflictor1.xlsx]Sheet1!$A$3:$A$365,0),MATCH(A5,[Deconflictor1.xlsx]Sheet1!$B$1:$U$1,0)),"")

PM Name

=IFERROR(INDEX([Deconflictor1.xlsx]Sheet1!$B$1:$U$1,SMALL(IF(([Deconflictor1.xlsx]Sheet1!$B$3:$U$365<>"")*(MOD(COLUMN([Deconflictor1.xlsx]Sheet1!$B$1:$U$1),2)=1)*([Deconflictor1.xlsx]Sheet1!$A$3:$A$365=A$2),COLUMN([Deconflictor1.xlsx]Sheet1!$B$1:$U$1)-COLUMN([Deconflictor1.xlsx]Sheet1!$B$1)+1,""),ROW($A1:A1))),"")


PM Reason

=IFERROR(INDEX([Deconflictor1.xlsx]Sheet1!$B$3:$T$365,MATCH(A$2,[Deconflictor1.xlsx]Sheet1!$A$3:$A$365,0),MATCH(C5,[Deconflictor1.xlsx]Sheet1!$B$1:$U$1,0)+1),"")

ALL entered with Ctrl+Shift+Enter

Copy down rows

[Hambone70]

Thanks, John. That works perfectly.

I would like the deconflictor to be oriented with names in the a column and the dates in a row. Is there a reason that your deconflictor has to have the dates in a column and names in a row?

And, rather than extracting a week at a time, I could transfer the entire year to another spreadsheet, and HLOOKUP to get the names and reasons into the weekly schedules.

Thanks again!

[JohnTopley]

You can transpose the names and dates if required; it's usual to have dates in the column but there are no hard and fast rules.

[Hambone70]

Double post...

[Hambone70]

I finally got the vertical deconflictor to work with the weekly schedule a week at a time (see attached files).

However, because of the way we work, a horizontal deconflictor works better. I'm trying (unsuccessfully) to get the attached horizontal deconflictor to work. I assigned named ranges and an INDIRECT technique to help, but I can't follow the formula logic to turn things around.

[JohnTopley]

See attached: for reason you replace with INDEX (Match .....,Match....) as per vertical

Usual formulae

=IFERROR(INDEX('[horizontal-deconflictor-1.xlsm]Data'!B$3:B$13,SMALL(IF('[horizontal-deconflictor-1.xlsm]Data'!$B$3:$B$13<>"",ROW($A$3:$A$13)-ROW($A$3)+1,""),ROWS($A$3:B3)),'[horizontal-deconflictor-1.xlsm]Data'!$B$1=B$2),"")


I must confess this seems a lot of effort for not much reward. You are simply reformatting the deconflictor data for (in my view) no added value.

For the time spent on this it could have been done manually!!!

The performance of the spreadsheets is also severely impacted

Why not have the "Deconflictor" in the same workbook?

[Hambone70]

Hi John

We maintain a separate annual deconflictor for a few reasons. First, we build and use the schedules a week at a time, as the daily schedule draws its data from the weekly schedule, and it bogs down with more than a month at a time. We delete and archive the weekly schedules at the end of the month. Also, we use the deconflictor to manage leave, availability, etc, and it's much easier to see it all on one worksheet.

Thanks again for your help. Did you intend to attach a file to your last post?

[JohnTopley]

Sorry ... see attached

FYI: I have been developing a VBA version to provide the Weekly file from the (horizontal) Deconflictor.

As the tabs are used to generate dates, what do you do when a week crosses the end/start of months.

Using VBA it would be easier for the macro to create the dates and name the tabs (I think!)

[Hambone70]

Hi John

Sorry for the late reply. I have too many projects on the go at present!

I'm not very VBA-savvy, but I do have a basic understanding.

Regarding a week crossing the end of a month, the formula in my previously attached weekly schedule takes care of that.

I'm trying to get my boss to authorize funds for commercial services, so I can pay for help.

Thanks again!

[JohnTopley]

Wrong thread !!!