Need a genius solution

[ThomasA83]

Hi all,

I am trying to solve below issue, but cant think of a way to solve it:

All the columns has alot more data, with more groups/fruits/clients etc., this is only a short version.

I need to create a Macro to help me converting the Data to the Convertion template, based on which Group is difined in Cell B17. Does anyone have an idea how I can do that?

Looking forward to hear your ideas?

Brgds
Thomas

 

[Yard]

A Pivot Table might be the quickest route forward. Drag the Group field into the Page Area and all the other relevant fields into the Row area and play around with sorting etc.. The only issue you might need to get around is that common values won't be repeated in each row, and you may have to create a dummy field to repeat the Group data (so you can have it in the Page area and the Row area).

 

[Andrew Poulsom]

A pivot table:

<TABLE style="WIDTH: 298pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=395 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" span=3 width=94><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>Group</TD><TD class=xl27 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 37pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" width=49 x:num="2">2 </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 71pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=94></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 71pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=94></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 71pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=94></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> </TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Data</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> </TD><TD class=xl28 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Client</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Fruit</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Sum of Price 1</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Sum of Price 2</TD><TD class=xl30 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Sum of Price 3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Client1</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Apples</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="59">59 </TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="84">84 </TD><TD class=xl30 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="103">103 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Mango</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="62">62 </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="87">87 </TD><TD class=xl31 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="104">104 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Client2</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Apples</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="61">61 </TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="85">85 </TD><TD class=xl30 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="101">101 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17> </TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent">Mango</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="57">57 </TD><TD class=xl34 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="80">80 </TD><TD class=xl35 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="100">100 </TD></TR></TBODY></TABLE>

 

[rockravi]

sds

 

[ThomasA83]

Yes I have thought of the Pivot table, but the thing is, I need the data in the format as per the Convertion list. Thats why I need some sort of macro to help me out.
I was thinking about some sort of if you could lookup if 2 (value in B17) is named in culumn F, then it shall return the information of all group 2, and the information shall be in same format as per the convertion list.
Any ideas?

 

[MickG]

Hi, Try this:-
NB:- Enter Group number for selection in "Input Box
NB:- Alter the destination Range at Bottom of code to suit.

Sub Fruit()
Dim Rng As Range, Ray, Price As Integer, Dn As Range, Grp As String
Dim d As Long
Set Rng = Range(Range("F3"), Range("F" & Rows.Count).End(xlUp))
On Error Resume Next
Grp = Application.InputBox(prompt:="Please Insert Group ", Title:="Group Selection", Type:=2)
    If Grp = False Then Exit Sub
        ReDim Ray(1 To Rng.Count, 1 To 5)
For Price = 1 To 3
    For Each Dn In Rng
        If Dn = Grp Then
            d = d + 1
            Ray(d, 1) = Dn.Value
            Ray(d, 2) = Dn.Offset(, -4)
            Ray(d, 3) = "Price " & Price
            Ray(d, 4) = Dn.Offset(, -5)
            Ray(d, 5) = Dn.Offset(, -4 + Price)
        End If
Next Dn
Next Price
Range("A17").Resize(d, 5).Value = Ray

Regards Mick

 

[Yard]

Yes I have thought of the Pivot table, but the thing is, I need the data in the format as per the Convertion list. Thats why I need some sort of macro to help me out.
I was thinking about some sort of if you could lookup if 2 (value in B17) is named in culumn F, then it shall return the information of all group 2, and the information shall be in same format as per the convertion list.
Any ideas?

Using a Pivot Table doesn't preclude you from ending up with the result you want - it provides the intermediate step. Once you have the Pivot Table, you just need a range adjacent to the table to reflect the values for the blank cells.

i.e. if your Pivot Table column headers go from A3:E3 then use this in, say, F3 and copy down and across:

=IF(A3="",F2,A3)

However, if you're fixated on a VBA solution then it looks like you have one above?

 

[ThomasA83]

Thanks MickG, your solution is what I were seraching for
However for Price 2 and Price 3 it gives #N/A info on all columns, do you know why?

 

[MickG]

Hi, Do you get this "#N/A" with you new Data or your test Data.
For the code, I set your Data Headers in row(2) column "A" and the actual Data in Range "A3" to "F14".
For a start I should Make sure the Results start away from those columns, say Column "H" on.
I can't at the moment replicate your error, but will keep trying.
The "#N/A" usually occurs when the Array you filling is larger than the Data to fill it.
Regards Mick

 

[ThomasA83]

Hi MickG,
Seems like it works like a charm, it was in the Test data it didn't work. On my real Data it works nearly perfect, however I am now strugling to Price type, which shall return 20', 40' and 40'HC instead of Price 1, Price 2 and Price 3.
Thanks for your help so far. Really appreciated.