Little problem

  • Thread starter Thread starter bojan0810
  • Start date Start date
B

bojan0810

I have little problem. Again.

https://dl.dropboxusercontent.com/u/57916703/Origianl_file_Analytics NEW3..xlsm

This is the file.

When you hit button on Dataset1 it looks words on J column and if column B has that words it moves to Black. If it doesnt have it moves to white.
So that works even it takes a while...

Now. My problem is on "Action" Sheet. Column A compares 2 columns, it compares column A on black and column B on white. If it finds on both sheet thenleaves blank if it is only on white, then it write white value.

But, it doesnt working like that, for first file it worked like a charm, I changed data and now it doesnt work like that.

For example on white sheet in column a there is code(A3) 2453218, that codeisnt in black sheet and it should write 2453218 in A3 on Action sheet. Instead of that it writes N/A. I tried to "convert" cells to numbers because of error, but that isnt a problem.

I hope someone can help me with this.
 
Hi Bojan,

Am Fri, 16 May 2014 11:12:16 -0700 (PDT) schrieb (e-mail address removed):
Now. My problem is on "Action" Sheet. Column A compares 2 columns, it compares column A on black and column B on white. If it finds on both sheet then leaves blank if it is only on white, then it write white value.
Click to expand...

change the formula in "Action" A2 to:
=IF(White!A2="","",IF(ISNUMBER(MATCH(White!A2,Black!A:A,0)),"",White!A2))


Regards
Claus B.
 
change the formula in "Action" A2 to:

=IF(White!A2="","",IF(ISNUMBER(MATCH(White!A2,Black!A:A,0)),"",White!A2))
Click to expand...

huh... That worked like an charm. Do you know why mine formula didnt worked?

And thanks for formula. I thought I tried all formulas, but I guess I didnt lol
 
Hi Bojan,

Am Fri, 16 May 2014 12:03:21 -0700 (PDT) schrieb (e-mail address removed):
And thanks for formula. I thought I tried all formulas, but I guess I didnt lol
Click to expand...

your formula gives a #NA if the number from White is not in Black.

To fasten your code try:

Sub Rectangle1_Click()
Columns("C:F").Clear

Dim pvt As PivotTable
Dim sh As Worksheet

Application.ScreenUpdating = False
Dim ListOfWords As Variant, EndRw As Long
'''Amend the following range to the real range containing your list of
words
ListOfWords = Range("J1:J" & Cells(Rows.Count, "J").End(xlUp).Row)
EndRw = Cells(Rows.Count, "B").End(xlUp).Row
For i = LBound(ListOfWords) To UBound(ListOfWords)
If WorksheetFunction.CountIf(Range("B2:B" & EndRw), "=*" &
ListOfWords(i, 1) & "*") > 0 Then
Range("A1:B" & EndRw).AutoFilter field:=2, Criteria1:="=*" &
ListOfWords(i, 1) & "*"
Range("A2:B" & EndRw).SpecialCells(xlCellTypeVisible).Copy _
Sheets("Black").Cells(Rows.Count, "A").End(xlUp)(2)
End If
Next i
ActiveSheet.AutoFilterMode = False

Dim sourceColumn As Range, targetColumn As Range

Set sourceColumn = Worksheets("Dataset1").Columns("A:B")
Set targetColumn = Worksheets("White").Columns("A:B")

sourceColumn.Copy Destination:=targetColumn

For Each sh In Worksheets
For Each pvt In sh.PivotTables
pvt.RefreshTable
Next pvt
Next sh
'Columns("a:b").Clear
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
 
Wow. Now its like formula got new processor installed. Much much better and faster.

I tested it with few different datas that I had, one is shorter one is much longer then that I showed you. And it worked great. With last code it would take much longer to finish.

Thanks for this.
 
I kinda got into problem.

I have 3007 rows of data(3006 if you dont count headers)

After "coding" black gets 3609 rows of data and white 3007 rows. Not sure what is problem
 
Hi again,

Am Sun, 18 May 2014 00:47:54 -0700 (PDT) schrieb (e-mail address removed):
I have 3007 rows of data(3006 if you dont count headers)

After "coding" black gets 3609 rows of data and white 3007 rows. Not sure what is problem
Click to expand...

try it with following code:

Sub Rectangle1_Click()
Columns("C:F").Clear

Dim pvt As PivotTable
Dim sh As Worksheet

Application.ScreenUpdating = False
Sheets("Black").Range("A2:B10000").ClearContents

Dim ListOfWords As Variant, EndRw As Long
'''Amend the following range to the real range containing your list of
words
ListOfWords = Range("J1:J" & Cells(Rows.Count, "J").End(xlUp).Row)
EndRw = Cells(Rows.Count, "B").End(xlUp).Row
For i = LBound(ListOfWords) To UBound(ListOfWords)
If WorksheetFunction.CountIf(Range("B2:B" & EndRw), "=*" &
ListOfWords(i, 1) & "*") > 0 Then
Range("A1:B" & EndRw).AutoFilter field:=2, Criteria1:="=*" &
ListOfWords(i, 1) & "*"
Range("A2:B" & EndRw).SpecialCells(xlCellTypeVisible).Copy _
Sheets("Black").Cells(Rows.Count, "A").End(xlUp)(2)
End If
Next i
ActiveSheet.AutoFilterMode = False

With Sheets("Black")
EndRw = Cells(Rows.Count, 1).End(xlUp).Row
.Range("A1:B" & EndRw).RemoveDuplicates Columns:=Array(1, 2), _
Header:=xlYes
End With

Dim sourceColumn As Range, targetColumn As Range

Set sourceColumn = Worksheets("Dataset1").Columns("A:B")
Set targetColumn = Worksheets("White").Columns("A:B")

sourceColumn.Copy Destination:=targetColumn

For Each sh In Worksheets
For Each pvt In sh.PivotTables
pvt.RefreshTable
Next pvt
Next sh
'Columns("a:b").Clear
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
 
Hi Claus. Thanks for answering...

I am getting error on
..Range("A1:B" & EndRw).RemoveDuplicates Columns:=Array(1, 2), _
Header:=xlYes

Maybe I just copied wrong
 
I think I copied wrong then.

I have slighty problem. Well at end on action what I need codes only they are ok. Because white ones are all rest that arent on black list...

But can I move on white sheet just remaining one that arent on black list?

Even as I said that isnt important much because I need "action" sheet at end only...

Thanks for helping me.
 
Hi,

Am Sun, 18 May 2014 03:22:48 -0700 (PDT) schrieb (e-mail address removed):
But can I move on white sheet just remaining one that arent on black list?
Click to expand...

have another look for "Analytics".


Regards
Claus B.
 
Hi there Claus. Thanks again for helping me lol. Maybe you are getting annoyed with me already lol...

Anyway, after adding new data with new words etc, black is doing all, white only gets codes and then I am getting error "Type mismatch" at

Sheets("White").Range("B2").Resize(rowsize:=UBound(arrOutB) + 1) = Application.Transpose(arrOutB)

Thanks
 
Hi,

Am Sun, 18 May 2014 07:40:58 -0700 (PDT) schrieb (e-mail address removed):
Anyway, after adding new data with new words etc, black is doing all, white only gets codes and then I am getting error "Type mismatch" at

Sheets("White").Range("B2").Resize(rowsize:=UBound(arrOutB) + 1) = Application.Transpose(arrOutB)
Click to expand...

try the new "Analytics"


Regards
Claus B.
 
Hi Bojan,

Am Sun, 18 May 2014 10:40:36 -0700 (PDT) schrieb (e-mail address removed):
unfortunately still same.
Click to expand...

I forgot a limitation in arrays. If you want to transpose an array the
count of characters can be max. 255. But in your data you have some
hyperlinks with much more characters. Now I have cut the count of
characters in "White".
Please look again for "Analytics"


Regards
Claus B.
 
Hi Bojan,

Am Sun, 18 May 2014 21:18:01 +0200 schrieb Claus Busch:
Please look again for "Analytics"
Click to expand...

if you need the complete length of the hyperlinks then look for
"Analytics2"


Regards
Claus B.
 
Hi Bojan,

Am Sun, 18 May 2014 21:45:59 +0200 schrieb Claus Busch:
if you need the complete length of the hyperlinks then look for
"Analytics2"
Click to expand...

I changed again the code because you had some words twice in "Dataset1"
column J
Now such errors will be solved with the new code.
The changing is not made in "Analytics" because I would prefer to use
"Analytics2"


Regards
Claus B.
 
Back
Top