• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF then copy data to other sheet

dreknapalci

New Member
Hi,

In 'Data' you have column with title F and one with title A. You have “source”. I need to process the source and to fill the sheets “F” and A” :

In the 'source' you for example this line (row 9):
TV10,

506778003,

40900720163

,03-09-2015, 22:46:16,

66,

1

Since the 40900720163 appears in “Data” under F, I want to add the +972506778003 in sheet “F” (if 40900720163 was under “A” I would add it to sheet A).

Thats it.

Can it be made with a button to process it every time I change the content of “Source” or “Data”. Can it be done without VBA (I dont mind vba or formulaes...)

Thanks!
 

Attachments

  • a.xls
    776 KB · Views: 4
Hi:

Is +972506778003 going to be static across or from where you are getting this value?
40900720163 is repeating around 27 times, how many times do you want +972506778003 to show in tab F or A?

Thanks
 
Button on Data sheet in the attached.
Is this going in the right direction?
 

Attachments

  • Chandoo26685_a.xls
    804.5 KB · Views: 9
Hey guys, thanks for taking your time into my issue:

let me clear it out a little bit more, but p45cal, you are on the right track!

Source sheet, row 9:

"Since the 40900720163 appears in “Data” under F, I want to add the +972506778003 in sheet “F” (so basically +972&column b value . If 40900720163 was under “A” I would add it to sheet A)"

Lines where column C contains something which is not in “DATA” should be ignored.

Thanks.
 
Hey @Nebu, one more thing:

I see that in the results there are numbers that appears more than once. Is it possible to make them appear only once ?

Regards
 
Hi:

Find the attached. I have included the code to remove the duplicates.

Thanks
 

Attachments

  • Chandoo26685_a.xlsb
    175 KB · Views: 6
Hey Nebu and everybody else.

I have similair problem, see attached file.

I have 3 sheets F, AR and AD. I need to write something which process them into F-RES, AR-RES and AD-RES sheets, with the following rules:

1.Process only numbers with 9 digits.

2. Add "972" at the beginning of the number

3.Make sure each number appears only once per sheet.

Of course with the button in DATA sheet like you made me last time.

Thank you!
 

Attachments

  • full.xlsx
    813 KB · Views: 2
Hi:

Find the attached.
Code:
Sub test()
Application.ScreenUpdating = False

Const prefix = "972"
Const sh = "-RES"

Dim rng As Range, nrng As Range
Dim arr As Variant
Dim wk As Worksheet
Dim shn As String

For Each wk In Worksheets
   
    If wk.Name <> "DATA" And wk.Name <> "F-RES" And wk.Name <> "AR-RES" And wk.Name <> "AD-RES" Then
       
        shn = wk.Name & sh
        Sheets(shn).Range("A1:A" & Sheets(shn).Cells(Rows.Count, "A").End(xlUp).Row).ClearContents
        Set rng = wk.Range("A1:A" & wk.Cells(Rows.Count, "A").End(xlUp).Row)
       
        rng.Copy Destination:=Sheets(shn).Range("A1")

        Set nrng = Sheets(shn).Range("A1:A" & Sheets(shn).Cells(Rows.Count, "A").End(xlUp).Row)
        nrng.RemoveDuplicates Columns:=Array(1), Header:=xlNo
        arr = nrng

        For i& = LBound(arr) To UBound(arr)

            Sheets(shn).Cells(i, 2) = Len(arr(i, 1))
            Sheets(shn).Cells(i, 1) = prefix & arr(i, 1)
       
        Next

        For j& = Sheets(shn).Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
       
        If Sheets(shn).Cells(j, 2) <> 9 Then Sheets(shn).Rows(j).EntireRow.Delete
       
        Next
       
            Sheets(shn).Range("B1:B" & Sheets(shn).Cells(Rows.Count, "B").End(xlUp).Row).ClearContents
       
        End If

Next

MsgBox "Done!!!", , "CopyPaste"
Application.ScreenUpdating = True

End Sub
Thanks
 

Attachments

  • full.xlsb
    858.8 KB · Views: 6
Hi Nebu, thanks! :)

Can you please add something:
  • If the number starts with 972 and has 12 digits please do not add 972 (just add it to the list the way it is, if it is not there already).
  • If it has less than 12 digits, do not add it to the results. Simply ignore it.
  • If the number starts with 0 or with 9720 , remove it.

Thanks :)
 
Back
Top