Grouping of Data

[dwichmann]

Hi, after some suggestions on the best way to group data. I have a spreadsheet laid out similar to one ive made up below

Orders A B C D
Fruit Apple Orange Orange Orange
Quantity 5 2 2 2


(above is completely fictional but the best way of explaining what im trying to do) I would like to be able to group the fruits and sum the quantitys ordered.

I.e.

Fruit Quantity

Apple 5
Orange 6

I cant change the way that this spreadsheet is structured and i really need a formula based response.

Please help

 

[xlbo]

why can you not change the structure?

You do realise that if you continue with this structure, the most items you can list will be 255......

To be honest, this is one of the worst ways of storing data. Unless there is a really good reason for it to be stored like this, your best bet is to get the data output changed

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dwichmann]

i realise this, its a costing spreadsheet ive inheritied. Unfortunately its huge but the number of types is never greater than 8 so limitations are not a problem. Changing the structure is simply not an option. Any suggestions other than this are greatly appreciated

 

[xlbo]

Changing the structure is simply not an option

Why?

The simplest solution to your issue would be to hard code a set of cells which reference the (maximum) 16 cells in which the data is held

If your data is currently in Range A1:I2

then in

A3 enter "Type"
B3 enter "Quantity"

A4 =B1
B4 =B2
A5 =C1
B5 =C2

etc etc

Yoiu will then have a structure which can be used for grouping. Easiest way to do this is to slap a pivot table over the new set of referenced data which should do exactly what you want

The BEST solution would be to change the structure - very bad practice IMO

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dwichmann]

Thanks for response, completely agree with structure, would never do this myself and is indeed very bad practise though not an option. I have now achieved what i wanted using a series of Hlookups.

 

[Welshbird]

Wouldn't a pivot table give you better options here?

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[WinblowsME]

Here's an alternative solution.

Sub Group_Data()
   Dim dict As Variant, curr_key As Variant
   Dim j As Integer, last_col As Integer
   Dim fruit As String, quantity As Integer
   
   Set dict = CreateObject("Scripting.Dictionary")
   last_col = ActiveSheet.Cells.Find(What:="*", SearchDirection:=xlPrevious, _
                                     SearchOrder:=xlByColumns).Column

   For j = 2 To last_col
      fruit = Cells(2, j)          
      quantity = CInt(Cells(3, j)) 
      dict.item(fruit) = dict.item(fruit) + quantity
   Next j
   
   For Each curr_key In dict.Keys
      Debug.Print curr_key & " " & dict.item(curr_key)
   Next
End Sub