Combining and reformatting tables into one data sheet

[Xen]

Hello,
I'm stuck with a problem where I have multiple tables following one pattern, which is not really suitable for analysis and further processing.
______________________________________________________
File attached should make things clear, I will try to explain it in words:
Columns represent hour when purchase was made, some stores might open/close at different times.
In rows you have 3 fields: date (only written once), then number of products sold and revenue.
Each time a new date is added, table gets 3 new rows.

______________________________________________________
First thing I need, is reformat this table and make data suitable for PivotTable, in this format (5 columns):
Store name, date, hour, sales, revenue

And this preferably should be done without use of VBA. I've solved this, but not in a very attractive way:
Pre-made a sheet for each store and with store/date/hour columns pre-filled, and each sales/revenue cell referring to specific location in table. Dates that are not yet filled have zero valies. This simplifies process a lot, yet still has much manual work to it.
I suspect that there is more elegant way to reformat table like this. I've been playing with index-match, counting ranges and such, ended up with huge formula that did not work after all.
So, can anyone tell me where should I start? Store name is easily extracted from sheet/filename, then (biggest problem) it somehow has to find how many dates are there, how many hours shop worked and fill values accordingly, then do some complex matching where it looks vertically for date, then horizontally for hour.
______________________________________________________

Now, there is second part to this problem, where I have to append data from different store into one big table.
Say, we now have 'normal' data from each store. I imagine having same formula down to the bottom of the sheet, and said formula scans each file and fills cells with it's contents, then, when there is no more rows left in current file, it starts working with new one until it runs out of file. I've encountered this problem a few times where I have to fill values until no values left in one table, then get values from other table in same fashion.
Is there a non-VBA solution for this as well?
______________________________________________________

Telling me what formulas and tricks to use and where to start should be enough.
Thanks to anyone who has any suggestions!

 

[NARAYANK991]

Hi ,

You say that your existing solution is to use pre-made sheets for each store ; does this mean that actually you wish to do away with these individual store sheets if you get a proper formula-based solution ?

If so , what will be the new format of the data , where the store name is also included ?

Narayan

 

[Xen]

Thank you for reply, Narayan
Yes, actually I want to get rid of those pre-made sheets, as it consumes a lot of time, yet the info we get from those forms is not that valuable (but I still have to do it).
Store name is included in sheet name. I currently use this formula to extract store name from sheet:

=RIGHT(CELL("filename";Store1!A1);LEN(CELL("filename";Store1!A1))-FIND("]";CELL("filename";Store1!A1)))

I've included example I use to reformat data for single store now. After my post I've finally came up with working solution for matching relevant sales and revenue figures, so it's no longer a problem

The only thing left with reformatting now is making a list of stores and dates with hours next to them.
I somehow have to tell excel that if we have 11 working hours, it should show one date 11 times, then next date and so on. And actual working hours and days should be taken from sheet as they may vary.
My current theory is: First date is always in B2, so you do =B2, in next cell you do =if(Hour<Previous hour;Date in upper cell + 1; date in upper cell). Then the problem is how to get actual hours and then cycle them N times.

 

[NARAYANK991]

Hi ,

I have introduced one new sheet Sheet1 , where the individual store's opening and closing times for each date are specified ; for a holiday , put in H instead.

Now , on the tab What I don't know , there are formulae to insert the dates and times ; is this what you want ?

Narayan

 

[SirJB7]

Hi, Xen!

Give a look at this file:
https://dl.dropboxusercontent.com/u/60558749/Combining and reformatting tables into one data sheet - Combine and restructure data-1 (for Xen at chandoo.org).xlsm

It has 4 new worksheets:
StoreNFormula: passes from the awful format to the nice format
FormulaNSotre: yes, you guessed right, the inverse process
StoreNFormula (2): just a copy of 1st WS for 2nd store so as to test next WS
CombinedN: all stores data

The 2 1st sheets are intended for manual conversion between AA and NN formats (entering the source WS name in the yellow shaded cell in row 1), since I didn't get fully clear which will be your input data structure. BTW, AA stands for actual awful and NN for new nice. :D

In the worksheet CombinedN there're 2 dynamic named ranges defined:
CombinedTable: =DESREF(CombinedN!$A$1;;;CONTARA(CombinedN!$A:$A);5) -----> in english: =OFFSET(CombinedN!$A$1,,,COUNTA(CombinedN!$A:$A),5)
StoreList: =DESREF(CombinedN!$G$2;;;CONTARA(CombinedN!$G:$G)-1;1) -----> in english: =OFFSET(CombinedN!$G$2,,,COUNTA(CombinedN!$G:$G)-1,1)

This is the code behind the command button:

Option Explicit

Sub VoucherForTwoSixPacksOfCarlsberg()
    ' constants
    Const ksWSCombined = "CombinedN"
    Const ksDataCombined = "CombinedTable"
    Const ksWSStore = "CombinedN"
    Const ksDataStore = "StoreList"
    ' declarations
    Dim rngC As Range, rngS As Range, rngW As Range
    Dim lRow As Long
    Dim I As Long
    ' start
    Set rngC = Worksheets(ksWSCombined).Range(ksDataCombined)
    Set rngS = Worksheets(ksWSStore).Range(ksDataStore)
    With rngC
        If .Rows.Count > 1 Then
            .Range(.Rows(2), .Rows(.Rows.Count)).ClearContents
        End If
        .Parent.Activate
    End With
    lRow = 1
    ' process
    For I = 1 To rngS.Rows.Count
        With Worksheets(rngS.Cells(I, 1).Value)
            Set rngW = Range(.Range("A1").End(xlToRight), .Range("A1").End(xlDown))
        End With
        Set rngW = rngW.Offset(1, 0).Resize(Application.CountIf(rngW.Columns(1), "> ") - 1)
        rngW.Copy
        rngC.Cells(lRow + 1, 1).PasteSpecial xlPasteValues
        lRow = lRow + rngW.Rows.Count
    Next I
    ' end
    Application.CutCopyMode = False
    With Worksheets(ksWSCombined)
        .Activate
        .[A1].Select
    End With
    Set rngW = Nothing
    Set rngS = Nothing
    Set rngC = Nothing
    Beep
End Sub

As I don't know the actual names of your worksheets you should build the store worksheet name list in column G of combined WS, from row 2 in advance.

Just advise if any issue.

Regards!

 

[Xen]

Oooh, Narayan, that's the idea I've been looking for! Easy to understand formula (roughly and without holidays, if number of times this date mentioned is less than hours worked found in timetable, repeat, else - increase date, if I got it right) and creating timetable isn't as complicated and time consuming as whole data sheet. Very well done solution, thank you!

Now, SirJB! Your formula works fantastically and gets the job done well, but I can't really figure out how it works! I mean, the formula is very small and it works perfectly, yet it has some parts that are much more clever than me.
I've already figured out MOD part of equation, and how it returns desired column, but I've yet to find out how this part works:
(INT((ROW()-1+$K$1-1)/$K$1)*3-1
I assume that if you divide (current row + hours) by (hours), you get how many times that hour cycle is repeated in data, that you use this to refer to according line in table... wow... just wow! It's overly clever. I can't reproduce this myself.. yet Tho I'm still trying and testing it, will get how it works eventually! No need to explain yet
Regarding VBA, first, I wanted to thank you for taking your time to do it! I've been thinking of way to do it in formulas, but came to conclusion that even if it is possible, it will be very slow to recalculate and excessively hard to modify for other/new forms. I see your VBA is working perfectly, yet amount of code combined with my VBA knowledge suggest that I will need even more time figure it out. I will take my time, tho Thank you very much!

So much new moves learned from you, Ninjas! Ranges with both absolute and relative references like this $B$2:B35 to make them expand dynamically. I somehow never even thought of that. Same goes for indirect/address combo, which now seems to be very sensible thing to do, I had no idea where to use address() before. Not even to mentions some smart logical tricks and techniques!
I've already improved myself a lot after reading Chandoo's articles, looks like I'll eventually become awesome as promised

I think the case is closed for now, things I've learned/got are enough for me to continue myself.
Thank you.

 

[Xen]

I'm very happy to announce that I've made a formula based data merging formula!
Using new techniques I discovered today, I realized it is not that hard actually!

I've included my example, that was made on top of SirJB's reformatted data. I did not combine it into single formula yet, but it should be more clear the way it is now, with additional columns + columns I used for debugging (that's how I figured out where I should add or subtract numbers in order to pull data from correct positions).

Idea is the following: We check how many data rows we have in each sheet (sheet name is the only thing entered manually) and make running total. Then we subtract this total (i.e. if we have 50 in first sheet, current total is 0, when we process next sheet we subtract 50 from current row number) from current row in order to get relevant row number in next sheet. It goes to indirect/address row number. Next sheet's name is pulled in same fashion, by comparing current row to total rows. This goes to sheet name in indirect/address formula. Column equals current column, as we are not reformatting but combining data.

If anyone googles this question in future and finds this thread, feel free to ask questions as I am subscribed to this thread.


Googling excel questions is how I found Chandoo

 

[SirJB7]

Hi, Xen!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!