• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Transpose Horizontal Data into Table with Formula

D

darwinkjs

New Member
Hi,

I am trying to build a simple entry table for a non-Excel user to enter data for billing (she is my daughter's ice skating coach). For each line, the user will enter various info on type of charge, length of time on ice, etc. Then, I would like her to be able to just put X's (in the same row) under the names of the girls for which that charge applies.

At the end, I would like to have a table where I can create a pivot table (with slicers) to allow her to choose the month and the student and create the invoice. I'm ok with almost all of it...I can get the individual rows to repeat for the number of students per section (this changes with each section). I know how small works, but for the life of me, I cannot figure out how to repeat the "small" formula for the number of X's (students) indicated.

I have uploaded a file as this will be far easier to see than I can explain. The "Entry Sheet" is where the coach will enter data. The Working DB is my attempt to create the database I need for the future pivot and slicers.

I really hope this makes sense. Thanks so much in advance! *fingers crossed*

Karolyn

p.s. I could not figure out how to post anything under "Introduce Yourself".
 

Attachments

  • Amely Template for Upload.xlsx
    25.5 KB · Views: 6
Are you open to revising your Data Entry Sheet to reflect each row being a record of a single transaction?
Yes you can write formulas that will "unpivot" your data, but they will be complex and given that you are writing this for someone who is not adept at Excel, will likely cause issues later. I assume,you'd prefer not to be stuck with maintaining an overly complex worksheet.

I've had a shot at creating a Table, Sales_Data, which I've placed on the tab Revised Entry Sheet. If Coach enters data this way, you'll avoid all the complexity of having to transform your entry data into "Working DB" using formulas. The Guys around here enjoy those challenges and you may well find someone has done it for you; my preference is simplify,simplify,simplify Especially when the workbook is to be handed to a non-Excel-er. Most of the fields are dropdown driven which limits the potential for data entry errors. In an Excel Table (and In Access) you can use CTRL-' to copy the field from the previous record

The only piece I'm not certain about is Col A on your Working DB, and I suspect that "Autres" could be any "Other" value, but I can see you are perfectly capable of amending those if they're awry.

Such a table as I've suggested will all Coach to Pivot to his or her heart's content, I believe.
 

Attachments

  • Amely Template -DME.xlsx
    34.9 KB · Views: 4
Thank you, David. I appreciate you taking the time to add in another tab even! Your suggestion is how I originally set it up, but I was trying to find a way to keep it simple for the user (and so much more complicated for me though...lol). I figured that if there were 7 students in her coaching session, it would be nice if she could just enter the information once, and add the X's by the student's names, rather than having to create 7 records. Was also thinking that if there were 7, but she accidentally missed entering one, she may end up missing a billing for one student.

I may end up going this way if I can't figure out a formula to make it work. Cause at this point I've spent just WAY too many hours with SMALL, MATCH, and INDEX trying to figure it out.

Thanks so much!
 
darwinkjs said:
..........
I may end up going this way if I can't figure out a formula to make it work. Cause at this point I've spent just WAY too many hours with SMALL, MATCH, and INDEX trying to figure it out.........
Click to expand...

Try,

In H3, array formula (to be confirmed enter with SHIFT+CTRL+ENTER instead of just ENTER) copied down :

=INDEX(BillingEntryTable[[#Headers],[Amanda]:[Yolanda]],RIGHT(SMALL(IF((BillingEntryTable[Date]=B3)*(BillingEntryTable[Type]=C3)*COLUMN($A$1:$X$1)*(BillingEntryTable[[Amanda]:[Yolanda]]="x"),ROW(BillingEntryTable[Amanda])/1%+COLUMN($I$1:$AF$1)),COUNTIFS(B$3:B3,B3,C$3:C3,C3)),2)-8)

Regards
Bosco
 

Attachments

  • Amely Template for Upload(1).xlsx
    27.1 KB · Views: 18
Last edited:
bosco_yip said:
Try,

In H3, array formula (to be confirmed enter with SHIFT+CTRL+ENTER instead of just ENTER) copied down :

=INDEX(BillingEntryTable[[#Headers],[Amanda]:[Yolanda]],RIGHT(SMALL(IF((BillingEntryTable[Date]=B3)*(BillingEntryTable[Type]=C3)*COLUMN($A$1:$X$1)*(BillingEntryTable[[Amanda]:[Yolanda]]="x"),ROW(BillingEntryTable[Amanda])/1%+COLUMN($I$1:$AF$1)),COUNTIFS(B$3:B3,B3,C$3:C3,C3)),2)-8)

Regards
Bosco
Click to expand...

Ok, so you are absolutely brilliant!! I am sitting in complete awe and trying to figure out exactly what this formula is doing, but perhaps I just accept and move on! Thank you bosco_yip, this is amazing! Why didn't I ask this question a week ago?!
 
@darwinkjs

Beautiful formula by @bosco_yip

Another option is to use Power Query. If you have Excel 2010 or 2013 you can install it as an add-in. With Excel 2016, it is available by default. Using this, you can unpivot the BillingEntryTable.

  • Just load the table to PQ (or Get & Transform Data as it is called in Excel 2016) and select the first few columns until Individual charge.
  • Right click and select "Unpivot Other Columns".
  • Remove the value attribute and load it back.
  • This will be a connection, so whenever user changes the data, just refresh the connection to get new table.
 
I hope @bosco_yip is on retainer - when it's time to add new students, services, etc. it will require his skill (or one of a few other Ninjas around here) to re-write it.
This formula is in many ways paradoxical - you have to apply something that's inherently complex in order to make the data entry "easier" for the user. I agree it's fun to see what formula gymnastics are produced around here and I learn a lot from them, but I also learn that the biggest issue is poor initial data structure.
Finding ways to unpviot data that's derived from a larger system is sometimes necessary; it should not be necessary on data you've created on a spreadsheet.
 
Hi ,

It always amazes me why people who use Excel for their daily work hate to use helper columns !

With helper columns , this problem can be solved so easily ; the solution itself can easily be understood , extended , modified by anyone who has a decent familiarity with Excel !

Narayan
 
NARAYANK991 said:
Hi ,

It always amazes me why people who use Excel for their daily work hate to use helper columns !

With helper columns , this problem can be solved so easily ; the solution itself can easily be understood , extended , modified by anyone who has a decent familiarity with Excel !

Narayan
Click to expand...

Are "helper columns" what was added into my formula by @bosco_yip? I was able to add students but didn't to change the formula a little. I ended up adding 60 students, which should be more than enough (*fingers crossed*) for my user. The formula works great, the only concern is the spreadsheet is a wee bit slow, especially when I tested it with a year's worth of data. May just need to turn off automatic calculations and show her how to use F9 !

Meantime, I'm going to play around with the Power Query option recommended by @r2c2 once I confirm she (the user) can get the add-in.

I really appreciate all the input here, it's been most helpful.
 
r2c2 said:
@darwinkjs

Beautiful formula by @bosco_yip

Another option is to use Power Query. If you have Excel 2010 or 2013 you can install it as an add-in. With Excel 2016, it is available by default. Using this, you can unpivot the BillingEntryTable.

  • Just load the table to PQ (or Get & Transform Data as it is called in Excel 2016) and select the first few columns until Individual charge.
  • Right click and select "Unpivot Other Columns".
  • Remove the value attribute and load it back.
  • This will be a connection, so whenever user changes the data, just refresh the connection to get new table.
Click to expand...

Thank you, I will try this. I haven't used it to Unpivot, but will work on that and see if it makes the calculations a little quicker. Will also need to see if the user is comfortable installing an add-in and working with it. We'll see!!
 
darwinkjs said:
Are "helper columns" what was added into my formula by @bosco_yip? I was able to add students but didn't to change the formula a little. I ended up adding 60 students, which should be more than enough (*fingers crossed*) for my user. The formula works great, the only concern is the spreadsheet is a wee bit slow, especially when I tested it with a year's worth of data. May just need to turn off automatic calculations and show her how to use F9 !

Meantime, I'm going to play around with the Power Query option recommended by @r2c2 once I confirm she (the user) can get the add-in.

I really appreciate all the input here, it's been most helpful.
Click to expand...
Hi ,

No. Bosco's formula does not make use of helper columns / cells ; it is to be entered in the cells where you want your output.

Helper columns / cells are cells which are used to make intermediate calculations ; the final output cells will then contain formulae which make use of the intermediate calculations in these helper columns / cells.

Narayan
 
NARAYANK991 said:
Hi ,

No. Bosco's formula does not make use of helper columns / cells ; it is to be entered in the cells where you want your output.

Helper columns / cells are cells which are used to make intermediate calculations ; the final output cells will then contain formulae which make use of the intermediate calculations in these helper columns / cells.

Narayan
Click to expand...

Ok understood. Thank you.
 
You must log in or register to reply here.
Back
Top