Reconfigure Data to make it usuable

[drewmorris12]

Hi all,

Thank you for all and any help in advance!

I have an export from a site (which I am unable to edit) which is giving the data in a layout that I am unable to use - it as got the week end date and then Mon, Tues, Weds... columns which means I can't do a SUMIF function as each row doesn't have it own date:

Is it possible to convert this:
Company, Week End, Location, Job Type, Mon, Tue, Wed, Thu, Fri, Sat, Sun,
ABC Company, 05/06/2022, Site 1, Job 1, 11, 11, 0, 11, 11, 11, 11,
DEF Company, 05/06/2022, Site 2, Job 1, 11, 0, 0, 11, 11, 11 11
GHI Company, 05/06/2022, Site 3, Job 4, 11, 11, 11, 0, 11, 0, 0,
JKL Company, 12/06/2022, Site 2, Job 2, 0, 0, 0, 0, 0, 11, 0,

To this:
Company, Day, Location, Job Type, Hours,
ABC Company, 30/05/2022, Site 1, Job 1, 11,
ABC Company, 31/05/2022, Site 1, Job 1, 11,
ABC Company, 01/06/2022, Site 1, Job 1, 0,
ABC Company, 02/06/2022, Site 1, Job 1, 11,
ABC Company, 03/06/2022, Site 1, Job 1, 11,
ABC Company, 04/06/2022, Site 1, Job 1, 11,
ABC Company, 05/06/2022, Site 1, Job 1, 11,
DEF Company, 30/05/2022, Site 2, Job 1, 11,
DEF Company, 31/05/2022, Site 2, Job 1, 0,
DEF Company, 01/06/2022, Site 2, Job 1, 0,
DEF Company, 02/06/2022, Site 2, Job 1, 11,
DEF Company, 03/06/2022, Site 2, Job 1, 11,
DEF Company, 04/06/2022, Site 2, Job 1, 11,
DEF Company, 05/06/2022, Site 2, Job 1, 11,
GHI Company, 30/05/2022, Site 3, Job 4, 11,
GHI Company, 31/05/2022, Site 3, Job 4, 11,
GHI Company, 01/06/2022, Site 3, Job 4, 11,
GHI Company, 02/06/2022, Site 3, Job 4, 0,
GHI Company, 03/06/2022, Site 3, Job 4, 11,
GHI Company, 04/06/2022, Site 3, Job 4, 0,
GHI Company, 05/06/2022, Site 3, Job 4, 0,
JKL Company, 06/06/2022, Site 2, Job 2, 0,
JKL Company, 07/06/2022, Site 2, Job 2, 0,
JKL Company, 08/06/2022, Site 2, Job 2, 0,
JKL Company, 09/06/2022, Site 2, Job 2, 0,
JKL Company, 10/06/2022, Site 2, Job 2, 0,
JKL Company, 11/06/2022, Site 2, Job 2, 11,
JKL Company, 12/06/2022, Site 2, Job 2, 0,

Example Reconfigure.xlsx

Thank you in advance!

[Glenn Kennedy]

Maybe this:

=LET(d,A2:D5,rd,ROWS(d),w,COUNTA(E1:K1),hr,INDEX(E2:K5,1+INT((SEQUENCE(rd*w)-1)/w),1+MOD((SEQUENCE(rd*w)-1),w)),CHOOSE({1,1,1,1,2},INDEX(d,1+INT((SEQUENCE(rd*w)-1)/w),SEQUENCE(,rd)),hr))

which will spill the results across & down.

[drewmorris12]

Hi Glenn,

Thank you very much - I have managed to get it so it is spilling down but it has created hundreds of columns all with N/A in after the initial 5 columns - any ideas?

Thanks,

Drew

[Glenn Kennedy]

I can not diagnose what I cannot see. However, double check ranges here in red:

=LET(d,A2:D5,rd,ROWS(d),w,COUNTA(E1:K1),hr,INDEX(E2:K5,1+INT((S

[Glenn Kennedy]

Did you drag the formula across? It just needs to be entered in ONE cell.

[Glenn Kennedy]

I can not reproduce ANYTHING similar to your description. I will need to see what you've done.

[drewmorris12]

Apologies - here is the file: Example Reconfigure (full).xlsx

Thanks Glenn!

Best wishes,

Drew

[Glenn Kennedy]

Infantile mistake by Yours Truly...

caused by there being the same number of columns in the Agency/Date/Location/Job bit as there were rows in your sample data (both were 4). Now fixed.

=LET(d,Sheet1!A2:D10,rd,ROWS(d),w,COUNTA(Sheet1!E1:K1),col,COUNTA(Sheet1!A1:D1),
da,INDEX(d,1+INT((SEQUENCE(rd*w)-1)/w),SEQUENCE(,col)),
hr,INDEX(Sheet1!E2:K10,1+INT((SEQUENCE(rd*w)-1)/w),1+MOD((SEQUENCE(rd*w)-1),w)),
CHOOSE({1,1,1,1,2},da,hr))

[drewmorris12]

Thank you Glenn - in your file it has kept the 'Week End' value - is it possible to have this calculated i.e. it will know if the week end is 05/06/22 the Monday will be 30/05/2022, Tuesday will be 31/05/2022 etc.

[Glenn Kennedy]

Many things are possible. But...

Sheet 1 cols A:D, rows 1 & 2. These are exactly the same. They cannot both be for the same week?? can they????

[drewmorris12]

Unfortunately they can - I had removed the job.user column which would be the distinguishing feature.

[Glenn Kennedy]

OK. Can I present the columns in a slightly different order (date immediately before hours)?

[drewmorris12]

Please do, there is no need to keep them in the order I have done

[Glenn Kennedy]

OK. A bit of tidying up, getting all the range definitions to the start, giving them (slightly) more memorable names, etc, etc. Check it carefully!!!

Please Login or Register  to view this content.

[drewmorris12]

Glenn - I cannot thank you enough - that has worked perfectly. I wouldn't have been able to do that in months let alone in a day so again thank you!

[Glenn Kennedy]

It keeps the "little grey cells" active on a thundery afternoon.



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.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.