Posts by mmdionisio

Hi,

I'm trying to detect blank cells and used vlookup to fill it up..

I attach an excel file for further visualization..

Thanks

Re: Merging Data that follows 2 conditions

Hi Herbds, yes I can used pivot but my boss is very choosy he wants me to used our company format not the pivot like format and for Excel 2010 I'm only using the excel 2007 I still didnt try it..poor me Dx

Re: Copy the separated word with the same item code

I think the problem is in here..

Function GetProd(ByVal txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "[^\-]+\-\d+ ?\-(.*)( Total)? "
        GetProd = Trim$(.Execute(txt)(0).submatches(0))
    End With
End Function

Re: Copy the separated word with the same item code

Hi jindon, your code works perfectly fine but when I manually check my data some cell returns what I actually want but some other cells returns #VALUE!

I attach file..

Thanks

Re: Merging Data that follows 2 conditions

anyone?

Re: Merging Data that follows 2 conditions

Ok thanks but is there other free software(lifetime) does things like that? :D..

or is there a vba codes to do that, so I can just add it to my other codes(vba subs)

Thanks..

Re: Merging Data that follows 2 conditions

Hi Herbds, unfortunately I'm already in the office and mediafire is not allowed here (filtered), so I googled AbleBits to find another installer and I found this
http://www.ablebits.com but the software is a free trial only(I need a lifetime :D) and yes it combines my data but the "quantity and price" didn't some up (I mean it becomes like this --> 2,4,6,7)

Thanks..

Hi Good Evening people

I'm trying to merge my data with the same "store code/store name"(because on my original data some of my store names does not have a corresponding store code) of the store names and "Item code", then the "quantity" and the "total amount" of the merge data will sum up.

I attach an excel file with expected result for further visualization..

Thanks

Re: Copy the separated word with the same item code

It's working I just need to see the codes

thanks

Re: Copy the separated word with the same item code

Hi jindon thanks but when I tried to view the codes a password box prompt..
may i know the password?

Thanks

*still have slower net.. ozgrid failed to upload my file so I'll just copy it here for better visualization
*you can copy this to your excel for better reading
- my last problem for one of my report

[TABLE="width: 869"]

Item Code

Customer

Brand

code-1

111 - cust1

[TD="colspan: 5"]*get the product name(separate it to "Total" and "code"[/TD]

code-1

112 - cust2

[TD="colspan: 7"]*the product name found on the bottom part of it's lists of customers and item code[/TD]

code-1 - prod1 Total

[TD="colspan: 7"]*what I'm trying to do is to copy the product name with the same item code
[/TD]

code-2

113 - cust3

code-2

114 - cust4

code-2

115 - cust5

code-2 -prod-2 Total

[TD="colspan: 2"]expected result[/TD]

etc…

[TD="colspan: 3"]Assume this is another worksheet[/TD]

.

cust

item code

prodname

.

111 - cust1

code-1

prod1

.

112 - cust2

code-1

prod1

.

113 - cust3

code-2

prod2

114 - cust4

code-2

prod2

115 - cust5

code-2

prod2

[/TABLE]

I've got a code to separate them but I do not know how to copy it like that
*w/c i also got it in here

Sub RemoveExtraWord()
'test
Dim i, n As Long, x, y As String
n = 1
Sheets("Sheet2").Columns(1).NumberFormat = "@"
For Each i In Range("A6:A" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(2)
y = Split(i, " -")(0)
x = Split(i, "-")(2)
x = Split(x, "Total")(0)
Sheets("Sheet2").Cells(n, 1).Resize(1, 2).Value = Array(y, x)
n = n + 1
Next i
End Sub

Thanks

Re: Formatting - VBA

Hi this is solved

I just add modify mumps code
from

lCol = wb.Worksheets("Sheet4").Range("IV1").End(xlToLeft).Column

to

lCol = wb.Worksheets("Sheet4").Range("IV1").End(xlToLeft).Column - 2

Thanks

Re: Formatting - VBA

Hi Jindon,

Your first code is already ok like mumps.. now you're 2nd code don't select the last 2 columns to become rows which is ok..

but my first criteria which is to skip the column that has no value(see my first post) didn't follow..

Thanks

Re: Formatting - VBA

Hi the codes of mumps are already ok, I just wanted to add what if I don't like to include the last 2 columns to become the rows..

anyone?

Thanks

Hi,

anybody knows the problem here..

Sheets("Master List").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Formula = "=TRIM(RIGHT(SUBSTITUTE(B2,"",REPT("",LEN(B2))),LEN(B2))) " 'location

the error says: Application-defined or object-define error

I already tried the formula manually in the excel and it's perfectly working..

I also have the same code like that..

Sheets("Master List").Cells(Rows.Count, "K").End(xlUp).Offset(1, 0).Formula = "=(I2*J2)" 'total

but it also works..

Thanks and Goodeve

Re: Formatting - VBA

or can I get your email, maybe i can send it to you there..

thanks

Re: Formatting - VBA

Hi jindon..

Is it ok I'll just copy it here, I got a slower net my file can't attach

[TABLE="width: 976"]

CODE

DESCRIPTION

Cost

Store Price

store Bacolod

store location4

other name location3

store name123 location2

store name location1

TOTAL

TOTAL

Aug-12

Aug-12

OFFTAKE

OFFTAKE

OFFTAKE

OFFTAKE

OFFTAKE

OFFTAKE

VALUE

111

prod1

46.80

84.75

3.00

2.00

53,453.00

453.00

222

prod2

[TD="align: right"]61.13[/TD]
[TD="align: right"]109.75[/TD]

7.00

26.00

46.00

19.00

11.00

453.00

4,534.00

[TD="colspan: 4"]* I ve got a long list of rows and columns so I'll just shorten it[/TD]

[TD="colspan: 4"]* I cant show the real data, a bit confidential :D[/TD]

[TD="colspan: 3"]assume this is a new worksheet using vba[/TD]

Store Code

Store Name

Area

Material ID

Comp Item Desc

Division

Item Code

Item Desc

Quantity

Price

[TD="colspan: 2"]Total Amount[/TD]

storebacolod

bacolod

[TD="align: right"]111[/TD]

prod1

[TD="align: right"]3[/TD]
[TD="align: right"]46.8[/TD]
[TD="align: right"]140.4[/TD]

store name location1

location1

[TD="align: right"]111[/TD]

prod1

[TD="align: right"]2[/TD]
[TD="align: right"]46.8[/TD]
[TD="align: right"]93.6[/TD]

store Bacolod

bacolod

[TD="align: right"]222[/TD]

prod2

[TD="align: right"]7[/TD]
[TD="align: right"]61.13[/TD]
[TD="align: right"]427.91[/TD]

store location4

location4

[TD="align: right"]222[/TD]

prod2

[TD="align: right"]26[/TD]
[TD="align: right"]61.13[/TD]
[TD="align: right"]1589.38[/TD]

other name location3

location3

[TD="align: right"]222[/TD]

prod2

[TD="align: right"]46[/TD]
[TD="align: right"]61.13[/TD]
[TD="align: right"]2811.98[/TD]

store name123 location2

location2

[TD="align: right"]222[/TD]

prod2

[TD="align: right"]19[/TD]
[TD="align: right"]61.13[/TD]
[TD="align: right"]1161.47[/TD]

[TD="colspan: 2"]* here's the correct format[/TD]

[TD="colspan: 5"]* store code, item, desc material id, div are naturally blank cells[/TD]

[/TABLE]
THANKS

Re: Formatting - VBA

hi jindon, actually on my original data I have different store names(not limited only to the store 1-7), so I think the condition will not work out.
How about I don't like to get the last 2 columns to become the rows (I just check all my original data and only column that I dont want to get is the last 2)

or the column who has a value = "TOTAL"

Thanks

Re: Formatting - VBA

hi mumps one more thing..what if i only want to get the store1-store6 not the entire column of stores(store1-store7)

Thanks

Re: Formatting - VBA

hi mumps thanks it works