# Pulling data from a table that changes size?

1. ## Pulling data from a table that changes size?

So, i need some help. I am moderately experienced with excel but i dont even know where to start with this one. I receive a district schedule from work every week in excel format. (the top spreadsheet) and i need it to "convert" into the lower spreadsheet to show a "at a glance" as to which store an employee is at for that day (don't care about the scheduled start and stop time, just the location/day/employee. This would be relatively simple if the spreadsheet always was exactly the same...but its not. Example would be for store 12345 sometimes has 2 employee scheduled at it or 1, or 3. This causes the merged cell with the store number on the left to always change. Any suggestions on how to start?

2. ## Re: Pulling data from a table that changes size?

One way:

=IFERROR(INDEX(LOOKUP(ROW(\$A\$3:\$A\$30),ROW(\$A\$3:\$A\$30)/(\$A\$3:\$A\$30<>""),\$A\$3:\$A\$30),MATCH(1,(\$B\$3:\$B\$30=\$A33)*(C\$3:C\$30<>""),0)),"Off")

copied across and down. see sheet.

3. ## Re: Pulling data from a table that changes size?

I just remembered... The formula MAY be an array formula in your version f excel. I tweaked it slightly in the file attached here.

SO, please refer to the attached file. IF the formulae are enclosed within a pair of { }, these are array formulae.

These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

4. ## Re: Pulling data from a table that changes size?

Looks like it is set up as an array, no issues there. I don't know why i didn't think of MATCH function. Works great! Thank you!!!

5. ## Re: Pulling data from a table that changes size?

You're welcome.

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

There are currently 1 users browsing this thread. (0 members and 1 guests)