Combine 2 worksheets into 1 1

[rillaru]

Hello,

I have a worksheet containing Year, month and Workdays as columns
eg :

2006 1 21
2006 2 18
2006 3 22
...

In this sheet all months of the year are present (for several years) and sorted.

In a second sheet, I have got data coming from an Oracle database including room number, year, month and occupation (days)

eg
1 2006 1 2.5
1 2006 3 5
.....

Problem is that for months that a room has not been occupied, there is no line in this secord sheet

I would like to combine the two sheets together in order to have for each month a row (giving 0 days of occupation in case of)

so for the previous examples the result should be

1 2006 1 2.5 21
1 2006 2 0 18
1 2006 3 5 22

How can I do this?

 

[xlhelp]

In Sheet3, Copy Column A of Sheet1 to Column A, Column B to B and Column C to D.

In Cell C1 type the following formula:
=INDEX(Sheet1!$C$1:$C$3,MATCH(1,(Sheet1!$A$1:$A$3=A1)*(Sheet1!$B$1:$B$3=B1),0))
and press CTRL/SHIFT/ENTER

and copy down

Member- AAAA Association Against Acronym Abusers

 

[rillaru]

Thanks for your answer. It gives me ideas for other sorts of problems

But this problem is a little more complicated:

The second sheet is coming from an Oracle database. If there is no record in th database for a certain month, then it does not enter a line into my Excel sheet. And that is what I should like to do (add a 0 line).

It is a sheet with different meeting rooms and the number of days they are occupied each month. So the list varies (let's say starting Jan 2006) till the current date (could be next year or later if still used).

SALLE YEAR MONTH OCCUPATION
1 2006 01 2,5
1 2006 02 0,5
1 2006 03 4
1 2006 04 4
1 2006 05 3
1 2006 06 8,5
1 2006 07 4
1 2006 09 3,5
1 2006 10 6,5
1 2006 11 9,5
1 2006 12 1
1 2007 01 5
1 2007 02 2,5
2 2006 01 4,5
2 2006 02 2,5
2 2006 03 7
2 2006 04 3
2 2006 05 9,5
2 2006 06 5
2 2006 07 3
2 2006 09 3,5
2 2006 10 9
2 2006 11 9
2 2006 12 1,5
2 2007 01 5
2 2007 02 5,5
2 2007 03 1
3 2006 01 10
3 2006 02 9
3 2006 03 12,5
3 2006 04 7,5
3 2006 05 9,5
3 2006 06 14
3 2006 07 7
3 2006 08 0,5
3 2006 09 7,5
3 2006 10 13,5
3 2006 11 12,5
3 2006 12 8,5
3 2007 01 8,5
3 2007 02 6
3 2007 03 0,5


The previous list is a copy of some date I get right now (sorry for the format). As you can see there are some months missing for room 1 (08/2006) and so on. I would like to have these lines inserted on the right roc and with number of days used (OCCUPATION) equal to 0

Right now I already can join the other data with formulas as follows (again sorry for the format)

SALLE YEAR MONTH OCCUPATION Libellé Capacité Location Mois Occupation Totale Jours Ouvrables Comparaison CompJours %
1 2006 01 2,5 VM-18 -2/36 (20 pers) 20 VM18 Janvier 2,5 21 120061 20061 11,90%
1 2006 02 0,5 VM-18 -2/36 (20 pers) 20 VM18 Février 0,5 20 120062 20062 2,50%
1 2006 03 4 VM-18 -2/36 (20 pers) 20 VM18 Mars 4 23 120063 20063 17,39%
1 2006 04 4 VM-18 -2/36 (20 pers) 20 VM18 Avril 4 17 120064 20064 23,53%
1 2006 05 3 VM-18 -2/36 (20 pers) 20 VM18 Mai 3 19 120065 20065 15,79%
1 2006 06 8,5 VM-18 -2/36 (20 pers) 20 VM18 Juin 8,5 21 120066 20066 40,48%
1 2006 07 4 VM-18 -2/36 (20 pers) 20 VM18 Juillet 4 20 120067 20067 20,00%
1 2006 09 3,5 VM-18 -2/36 (20 pers) 20 VM18 Septembre 3,5 21 120069 20069 16,67%
1 2006 10 6,5 VM-18 -2/36 (20 pers) 20 VM18 Octobre 6,5 20 1200610 200610 32,50%
1 2006 11 9,5 VM-18 -2/36 (20 pers) 20 VM18 Novembre 9,5 20 1200611 200611 47,50%
1 2006 12 1 VM-18 -2/36 (20 pers) 20 VM18 Décembre 1 20 1200612 200612 5,00%
1 2007 01 5 VM-18 -2/36 (20 pers) 20 VM18 Janvier 5 21 120071 20071 23,81%
1 2007 02 2,5 VM-18 -2/36 (20 pers) 20 VM18 Février 2,5 20 120072 20072 12,50%
2 2006 01 4,5 VM-18 -2/33 (20 pers) 20 VM18 Janvier 4,5 21 220061 20061 21,43%
2 2006 02 2,5 VM-18 -2/33 (20 pers) 20 VM18 Février 2,5 20 220062 20062 12,50%
2 2006 03 7 VM-18 -2/33 (20 pers) 20 VM18 Mars 7 23 220063 20063 30,43%
2 2006 04 3 VM-18 -2/33 (20 pers) 20 VM18 Avril 3 17 220064 20064 17,65%
2 2006 05 9,5 VM-18 -2/33 (20 pers) 20 VM18 Mai 9,5 19 220065 20065 50,00%
2 2006 06 5 VM-18 -2/33 (20 pers) 20 VM18 Juin 5 21 220066 20066 23,81%
2 2006 07 3 VM-18 -2/33 (20 pers) 20 VM18 Juillet 3 20 220067 20067 15,00%
2 2006 09 3,5 VM-18 -2/33 (20 pers) 20 VM18 Septembre 3,5 21 220069 20069 16,67%
2 2006 10 9 VM-18 -2/33 (20 pers) 20 VM18 Octobre 9 20 2200610 200610 45,00%
2 2006 11 9 VM-18 -2/33 (20 pers) 20 VM18 Novembre 9 20 2200611 200611 45,00%
2 2006 12 1,5 VM-18 -2/33 (20 pers) 20 VM18 Décembre 1,5 20 2200612 200612 7,50%
2 2007 01 5 VM-18 -2/33 (20 pers) 20 VM18 Janvier 5 21 220071 20071 23,81%
2 2007 02 5,5 VM-18 -2/33 (20 pers) 20 VM18 Février 5,5 20 220072 20072 27,50%
2 2007 03 1 VM-18 -2/33 (20 pers) 20 VM18 Mars 1 22 220073 20073 4,55%
3 2006 01 10 VM-18 -2/14 (38 pers) 38 VM18 Janvier 10 21 320061 20061 47,62%
3 2006 02 9 VM-18 -2/14 (38 pers) 38 VM18 Février 9 20 320062 20062 45,00%
3 2006 03 12,5 VM-18 -2/14 (38 pers) 38 VM18 Mars 12,5 23 320063 20063 54,35%
3 2006 04 7,5 VM-18 -2/14 (38 pers) 38 VM18 Avril 7,5 17 320064 20064 44,12%
3 2006 05 9,5 VM-18 -2/14 (38 pers) 38 VM18 Mai 9,5 19 320065 20065 50,00%
3 2006 06 14 VM-18 -2/14 (38 pers) 38 VM18 Juin 14 21 320066 20066 66,67%
3 2006 07 7 VM-18 -2/14 (38 pers) 38 VM18 Juillet 7 20 320067 20067 35,00%
3 2006 08 0,5 VM-18 -2/14 (38 pers) 38 VM18 Août 0,5 22 320068 20068 2,27%
3 2006 09 7,5 VM-18 -2/14 (38 pers) 38 VM18 Septembre 7,5 21 320069 20069 35,71%
3 2006 10 13,5 VM-18 -2/14 (38 pers) 38 VM18 Octobre 13,5 20 3200610 200610 67,50%
3 2006 11 12,5 VM-18 -2/14 (38 pers) 38 VM18 Novembre 12,5 20 3200611 200611 62,50%
3 2006 12 8,5 VM-18 -2/14 (38 pers) 38 VM18 Décembre 8,5 20 3200612 200612 42,50%
3 2007 01 8,5 VM-18 -2/14 (38 pers) 38 VM18 Janvier 8,5 21 320071 20071 40,48%
3 2007 02 6 VM-18 -2/14 (38 pers) 38 VM18 Février 6 20 320072 20072 30,00%
3 2007 03 0,5 VM-18 -2/14 (38 pers) 38 VM18 Mars 0,5 22 320073 20073 2,27%


(As you can see everything is in French, but since I am in Belgium we have to do this like that.)

All the other data comes from other sheets adnd is inserted with (mostly) VLOOKUP

Problem is that I should first get the months with 0 days in the original and I never know how many lines there are (normally more each month).

 

[SkipVought]

Hi,

Use MS Query to join the data in your yeo tables.

Using MS Query to get data from Excel faq68-5829

Use an OUTER JOIN to get ALL data from the one sheet and EXISTING data from the other (ORACLE) sheet.

Skip,
[sub]
[red][/red]
[/sub]

 

[SkipVought]

Hi,

Use MS Query to join the data in the two sheets. Use an OUTER JOIN so that ALL the rows from the first table are joined with any existing rows from the second (ORACLE) table.

Using MS Query to get data from Excel faq68-5829



Skip,
[sub]
[red][/red]
[/sub]