Combining tables of data using formulas

K

kraser

New Member
Joined
Feb 26, 2012
Messages
3
On Table 1 id love to have it automated with formulas so that when I fill in the tasks for my staff on tables 2, 3, and 4, Table 1 will look like table 5 automatically.

I dont need to worry about if I accidentally put the same person doing two things at once. That wont happen. Ideally id like the table to look up for the data in priority order of the tables numbers. (Table 2 first, Table 3 2nd and then table 4 last).

Please see attached picture

I cant seem to put my finger on it. Is it an IF/LOOKUP/

Please help, Thanks in advance.
 

Attachments

  • Screenshot 2023-09-20 at 02.40.56.png
    Screenshot 2023-09-20 at 02.40.56.png
    158 KB · Views: 10

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Need two things -

Use XL2BB tool to post your sample data
~ Update your Account details with Current Excel Version & Platform (Remember to scroll down & Save)

Solution depends upon the above
 
Upvote 0
try this:
Book1
ABCDEFGHI
154CombinedMondayTuesdayWednesdayThursdayFridaySaturdaySunday
155Staff 1
156Staff 2
157Staff 3
158Staff 4
159Staff 5
160
161DrinksMondayTuesdayWednesdayThursdayFridaySaturdaySunday
162Staff 1DrinksDrinks
163Staff 2DrinksDrinks
164Staff 3Drinks
165Staff 4Drinks
166Staff 5Drinks
167
168FoodMondayTuesdayWednesdayThursdayFridaySaturdaySunday
169Staff 1Food
170Staff 2FoodFood
171Staff 3FoodFood
172Staff 4Food
173Staff 5Food
174
175GiftsMondayTuesdayWednesdayThursdayFridaySaturdaySunday
176Staff 1Gifts
177Staff 2Gifts
178Staff 3GiftsGifts
179Staff 4GiftsGifts
180Staff 5Gifts
181
182CombinedMondayTuesdayWednesdayThursdayFridaySaturdaySunday
183Staff 1Drinks  GiftsFoodDrinks 
184Staff 2FoodDrinks  GiftsFoodDrinks
185Staff 3GiftsFoodDrinks  GiftsFood
186Staff 4 GiftsFoodDrinks  Gifts
187Staff 5  GiftsFoodDrinks  
kraser
Cell Formulas
RangeFormula
C183:I187C183=CONCATENATE(C162,C169,C176)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B161:I187Expression=B161="Gifts"textNO
B161:I187Expression=B161="Food"textNO
B161:I187Expression=B161="Drinks"textNO
 
Upvote 0
Check this and revert -

Book3
ABCDEFGHI
154CombinedMondayTuesdayWednesdayThursdayFridaySaturdaySunday
155Staff 1Drinks  GiftsFoodDrinks 
156Staff 2FoodDrinks  GiftsFoodDrinks
157Staff 3GiftsFoodDrinks  GiftsFood
158Staff 4 GiftsFoodDrinks  Gifts
159Staff 5  GiftsFoodDrinks  
160
161DrinksMondayTuesdayWednesdayThursdayFridaySaturdaySunday
162Staff 1DrinksDrinks
163Staff 2DrinksDrinks
164Staff 3Drinks
165Staff 4Drinks
166Staff 5Drinks
167
168FoodMondayTuesdayWednesdayThursdayFridaySaturdaySunday
169Staff 1Food
170Staff 2FoodFood
171Staff 3FoodFood
172Staff 4Food
173Staff 5Food
174
175GiftsMondayTuesdayWednesdayThursdayFridaySaturdaySunday
176Staff 1Gifts
177Staff 2Gifts
178Staff 3GiftsGifts
179Staff 4GiftsGifts
180Staff 5Gifts
181
182CombinedMondayTuesdayWednesdayThursdayFridaySaturdaySunday
183Staff 1Drinks  GiftsFoodDrinks 
184Staff 2FoodDrinks  GiftsFoodDrinks
185Staff 3GiftsFoodDrinks  GiftsFood
186Staff 4 GiftsFoodDrinks  Gifts
187Staff 5  GiftsFoodDrinks  
Sheet1
Cell Formulas
RangeFormula
C155:I159C155=IF(XLOOKUP($B155,$B$162:$B$166,C$162:C$166)=0, IF(XLOOKUP($B155,$B$168:$B$174,C$168:C$174)=0, IF(XLOOKUP($B155,$B$175:$B$180,C$175:C$180)=0, XLOOKUP($B155,$B$183:$B$187,C$183:C$187), XLOOKUP($B155,$B$175:$B$180,C$175:C$180)), XLOOKUP($B155,$B$168:$B$174,C$168:C$174)), XLOOKUP($B155,$B$162:$B$166,C$162:C$166))
C183:I187C183=CONCATENATE(C162,C169,C176)
 
Upvote 0
OR this one - If you want to join all the data...

Book3
ABCDEFGHI
154CombinedMondayTuesdayWednesdayThursdayFridaySaturdaySunday
155Staff 1Drinks  GiftsFoodDrinks 
156Staff 2FoodDrinks  GiftsFoodDrinks
157Staff 3GiftsFoodDrinks  GiftsFood
158Staff 4 GiftsFoodDrinks  Gifts
159Staff 5  GiftsFoodDrinks  
160
161DrinksMondayTuesdayWednesdayThursdayFridaySaturdaySunday
162Staff 1DrinksDrinks
163Staff 2DrinksDrinks
164Staff 3Drinks
165Staff 4Drinks
166Staff 5Drinks
167
168FoodMondayTuesdayWednesdayThursdayFridaySaturdaySunday
169Staff 1Food
170Staff 2FoodFood
171Staff 3FoodFood
172Staff 4Food
173Staff 5Food
174
175GiftsMondayTuesdayWednesdayThursdayFridaySaturdaySunday
176Staff 1Gifts
177Staff 2Gifts
178Staff 3GiftsGifts
179Staff 4GiftsGifts
180Staff 5Gifts
181
182CombinedMondayTuesdayWednesdayThursdayFridaySaturdaySunday
183Staff 1Drinks  GiftsFoodDrinks 
184Staff 2FoodDrinks  GiftsFoodDrinks
185Staff 3GiftsFoodDrinks  GiftsFood
186Staff 4 GiftsFoodDrinks  Gifts
187Staff 5  GiftsFoodDrinks  
Sheet1
Cell Formulas
RangeFormula
C155:I159C155=TEXTJOIN(" ,",TRUE,C162,C169,C176)
C183:I187C183=CONCATENATE(C162,C169,C176)
 
Upvote 0
How about:
Book1
BCDEFGHI
1CombinedMondayTuesdayWednesdayThursdayFridaySaturdaySunday
2Staff 1DrinksGiftsFoodDrinks
3Staff 2FoodDrinksGiftsFoodDrinks
4Staff 3GiftsFoodDrinksGiftsFood
5Staff 4GiftsFoodDrinksGifts
6Staff 5GiftsFoodDrinks
7
8DrinksMondayTuesdayWednesdayThursdayFridaySaturdaySunday
9Staff 1DrinksDrinks
10Staff 2DrinksDrinks
11Staff 3Drinks
12Staff 4Drinks
13Staff 5Drinks
14
15FoodMondayTuesdayWednesdayThursdayFridaySaturdaySunday
16Staff 1Food
17Staff 2FoodFood
18Staff 3FoodFood
19Staff 4Food
20Staff 5Food
21
22GiftsMondayTuesdayWednesdayThursdayFridaySaturdaySunday
23Staff 1Gifts
24Staff 2Gifts
25Staff 3GiftsGifts
26Staff 4GiftsGifts
27Staff 5Gifts
Sheet1
Cell Formulas
RangeFormula
C2:I6C2=C9:I13 & C16:I20 & C23:I27
Dynamic array formulas.
 
Upvote 0
Apologies @awoohaw I accidentally replied twice using your sample data. I thought OP has replied with sample data. I think I should have been more watchful. Regards
 
Upvote 0
SanjayGMusafir said:
Apologies @awoohaw I accidentally replied twice using your sample data. I thought OP has replied with sample data. I think I should have been more watchful. Regards
Click to expand...
No worries. You've offered other solutions that the OP can use. I often ask for xl2bb or table data, but sometimes if the effort is not much I recreate it. But, I hope the OP understands that the forum is more interested in finding solutions and not "setting" up the scenario. And, in the future will post sample data and not images, which are helpful, but often insufficient to get a forum member to post a solution.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Option to combine single row from many tables into one new table
Replies
3
Views
169
awoohaw
awoohaw
D
  • Question
Display/Extract table content into a different table... possibly a Pivot?
Replies
1
Views
150
Kerryx
K
K
  • Question
How to Use "GetPivotData" formula to retrieve data from a closed workbook
Replies
3
Views
311
RoryA
RoryA
S
  • Question
Macro to add sum total to track percentages
Replies
0
Views
107
spenserallen7
S
A
  • Question
Excel table protection with formula copying
Replies
2
Views
556
Allie_Excel
A

Forum statistics

Threads
1,215,077
Messages
6,122,992
Members
449,094
Latest member
masterms

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