Creating a List from a Table

[ndbennett]

Hi - my company supplies various groceries to multiple stores. Our inventory analyst visits the stores and prepares a table like the following, that we use to prepare the orders for dispatch:

Store 1 Store 2 Store 3 Store 4 ​

Product A 1 3
Product B 5 5
Product C 7 9
Product D 2

We use Access as an inventory management tool and currently manually copy the order data into it, but I would like to append via an upload. Can anyone advise whether is is possible to automatically convert the above table into a list like the following, and if so, how?

Store Product Count
Store 1 Product A 1
Store 1 Product D 2
Store 2 Product B 5
Store 3 Product A 3
Store 3 Product B 5
Store 3 Product C 7
Store 4 Product C 9

Thanks!

 

[tdh777]

Hi ND,

I 'd recommend that you consider this to be a small commercial request (it will save your business significant money by reducing labour costs every time orders are processed), but all answers given here are voluntary.

Suffice to say that it would be quite straight-forward to create a single-step function to do what you want which would take less than a couple of seconds to turn the input data table into the list you are looking for. (There's no single-step Excel function/process to do what you want, although it could be done by a number of steps / formulas).

Feel free to ask for more info if you like.

Cheers,
Warren K

 

[Peter_SSs]

It isn't clear if you are looking for a formula approach or a macro, so here is a suggestion for each.

Formula
Each formula copied down as far as you might need

Excel Workbook
	A	B	C	D	E
1		Store 1	Store 2	Store 3	Store 4
2	Product A	1		3
3	Product B		5	5
4	Product C			7	9
5	Product D	2
6
7
8	Store 1	Product A	1
9	Store 1	Product D	2
10	Store 2	Product B	5
11	Store 3	Product A	3
12	Store 3	Product B	5
13	Store 3	Product C	7
14	Store 4	Product C	9
15
Table to List Formula

Macro
With data starting in column A/row 1 as in A1:E5 below, try (in a copy of your workbook)

Sub Make_List()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  
  a = Range("A1").CurrentRegion.Value
  ReDim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 3)
  For j = 2 To UBound(a, 2)
    For i = 2 To UBound(a, 1)
      If Not IsEmpty(a(i, j)) Then
        k = k + 1
        b(k, 1) = a(1, j): b(k, 2) = a(i, 1): b(k, 3) = a(i, j)
      End If
    Next i
  Next j
  Range("A" & Rows.Count).End(xlUp).Offset(3).Resize(k, UBound(b, 2)).Value = b
End Sub

Data in rows 8, 9, 10, ... below was produced by the above code.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: center;;">Store 1</td><td style="text-align: center;;">Store 2</td><td style="text-align: center;;">Store 3</td><td style="text-align: center;;">Store 4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Product A</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Product B</td><td style="text-align: center;;"></td><td style="text-align: center;;">5</td><td style="text-align: center;;">5</td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Product C</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">7</td><td style="text-align: center;;">9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Product D</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Store 1</td><td style="text-align: center;;">Product A</td><td style="text-align: center;;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Store 1</td><td style="text-align: center;;">Product D</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Store 2</td><td style="text-align: center;;">Product B</td><td style="text-align: center;;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Store 3</td><td style="text-align: center;;">Product A</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Store 3</td><td style="text-align: center;;">Product B</td><td style="text-align: center;;">5</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Store 3</td><td style="text-align: center;;">Product C</td><td style="text-align: center;;">7</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Store 4</td><td style="text-align: center;;">Product C</td><td style="text-align: center;;">9</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr></table><p style="width:13.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Table to List vba</p>

 

[ndbennett]

Thank you so much for your input, much appreciated - and apologies for the horrible formatting on my original question.

 

[ndbennett]

Hi Walter, I agree that long term, professional automation is the way to go, but our budget is limited and the team still cling to their pens and paper. But Excel and Access are big steps forward for us! Thanks for taking the time to respond.

 

[Peter_SSs]

Thank you so much for your input, much appreciated

No problem, glad to help.

- and apologies for the horrible formatting on my original question.

For next time, have a look at the links in my signature block below for way to nicely show your sample data like in my post above.