You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
Can anyone help, I don't 'think' this is possible but I know very little in excel so could be totally wrong
I have an excel document containing 2 tables, the tables have the same info in them but portrayed in different order, I am looking for the 2nd (bottom) table to auto populate when the top table is completed. However, with it being different data I'm not sure if it is possible. I'll try and explain set up
Table 1 is a standard timetable. Name of people in column 1, dates across top and locations in the middle.
Table 2 has locations in column 1, dates across the top and names of people in the middle.
If a box in table 1, for Dave on Monday is inputted with 'Bristol', is there a formula that would auto complete table 2 with 'Dave' in the Bristol column for Monday?
Hope that makes sense, I can try and take a screen shot of the current document if that helps
Are the dates in the same cols for table both tables (eg col Z is 21st Jan 2023 in both)? That makes it a bit simpler but you probably need to look at MATCH and OFFSET and don't forget IFERROR to handle exceptions.
Pivot table or index match should work
Yes dates are the same, essentially one table tip of page and the other underneath it.
Quick googling said about OFFSET and MATCH but wasn't really sure what they meant 🤣 Will have a look at Pivot table as well
Pivot table is the obvious choice (other than use software designed for it)
Trying to bend the will of excel to do things it's not designed for is always a pain in the hoop.
I have half a mind that you should be able to do this in Project (MS or Libre, whatever)
Then produce a report based on location.
Trying to bend the will of excel to do things it’s not designed for is always a pain in the hoop.
I just use VBA in Excel, means you can do just about anything..
However, there is a small learning curve to get over first.
Trying to bend the will of excel to do things it’s not designed for is always
... the best bit.
Ftfy 😉
I’d use PowerQuery to do this.
Load the contents of table1 into PQ using Data-> From Table/Range. In PQ editor, select all the date columns, choose Transform and the ‘unpivot columns’ option. Choose the column called ‘attribute’ and then click ‘pivot column’. In the options presented the values need to be whatever your name column is called and in advanced options select ‘don’t aggregate’. Click back to the home tab and choose ‘close and load to’. Check the table option and select existing worksheet pick an area below table1.
Whenever you make a change in table1 you’ll need to click refresh in the data tab to update table2.
RM.
You can use a vlookup and a match function this video explains it:
… the best bit.
Ftfy 😉
I'll give you that. It can be very satisfying but it's like single speeding.
You do it despite yourself
This assumes that you have one person max in each place and that each person can only be in one place at once.
The second table will also need manually updating if you have a new location added.
And if you misspell a location then that ‘appointment’ just won’t show up
Other than that it’s great….
Edit: actually I've fixed most of those points!
Let’s say your ‘by person’ table is in cells A1-F8. Column A is person names. B1-F1 are “Monday” to “Friday”. Each cell in B2-F8 is either blank or has the place name in it.
The ‘by location’ table is in cells A14-F21. A is a list of locations (starting in A15). B14-F14 are “Monday”, “Tuesday” etc.
Put this formula in cell A15 to get a list of all locations in alphabetical order:
=SORT(UNIQUE(TOCOL((B2:F8))),1,1)
Put this formula in cell B15, then copy it across/down for all locations and days.
=IFNA(INDEX($A$2:$A$8,MATCH($A15,B$2:B$8,0),),"")
If you could have multiple people at one location then try
=(CONCAT(FILTER($A$2:$A$8,B$2:B$8=$A15,"")))
That will join the names of all the people in that location into one string e.g. if Amanda and Chipps are both in Grimsby on Monday then that box will say “AmandaChipps”