Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    fill in missing data (excel 2003)

    Greetings,
    In the attached sample all missing data for the given CSE_ID need to be filled first and then I would run the macro to delete the dups. So far, I have manually filled in the missing data and run the macro to delete the dups. For example for CSE_ID 50913, I copy the content of cell j2 and paste it in cell j3, m3 to m2 and m4, n3 to n2 and n4 etc. Once data is filled, I will then run the macro to delete the dups. This works fine but was wondering (since I have close to 20000 records):
    1. if this process can be automated, and
    2. once the process is done, how can I double check only one record exist in the CSE_ID column

    TIA
    OCM
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: fill in missing data (excel 2003)

    Please provide more info.
    - What to do if more than one value has already been filled in in a column for a given CSE_ID? Or is that impossible?
    - Which columns should be filled in? Should the hidden columns be included?

    You can easily add a column similar to the IsDup column but based on CSE_ID only.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: fill in missing data (excel 2003)

    How about this?

    I used advanced filtering on the CSE_ID column to extract a unique list of them into the extracted copy sheet. Then I created the Array formula (confirm with ctrl-shift-enter) in A2 and copied it down the column. Then I copied the formula range in col A to all the other columns (except the CSE_ID column of course).

    If you have more than one cell in any column that has a value (and you do) it takes the first one for that column
    Expand the range in formulas as desired.

    Steve
    Attached Files Attached Files

  4. #4
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fill in missing data (excel 2003)

    Hans,
    It doesn’t really matter what column should be filled in as long as they all look the same for the given CSE_ID in this case (50913).
    In the example attached, 304 should be filled in C6 R2 and 50575 should be filled in C9 and R2. C in C10 R2 etc.

    Yes, the hidden columns can be included, but mostly, the inconsistency appears starting DCD columns.

    TIA,
    OCM
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: fill in missing data (excel 2003)

    Steve's proposed solution appears to work very well, and you don't even need to run a macro to delete duplicates.
    You can use Edit | Copy, then Edit | Paste Special with the Values option to get rid of the formulas if you wish.

  6. #6
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    fill in missing data (excel 2003)

    Steve,

    Thanks for your reply. I was trying to adopt your example, what are the steps you took to make copies to the copy sheet, copy & paste after you highlight the CSE_ID column and applied advance filter, or did you choose filter the list and copy to another location and check the unique records only?
    If I wanted to copy the array formula you provided & paste special in cell A2, what is the steps I need to take?

    TIA
    OCM

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: fill in missing data (excel 2003)

    <P ID="edit" class=small>(Edited by sdckapr on 28-Nov-07 14:58. Oops forgot about column C)</P>I did it all manually. I used adv filter, filter the list and copy to another location and check the unique records only.

    After creating all the formulas, you can copy all the cells and paste-special them all. Then you have extracted all the unique records. You can then (after verifying with the source) delete the original. You can try this macro. It presumes the active sheet is the "source" and creates a new one with the unique CSE_ID codes using the proposed formulas. (yoiu could have the code delete the source sheet, but I prefer the option to make sure the extraction was correct and then delete it myself in case there was a code problem...)

    Steve
    This is a generalized macro. The brunt came from a recorded macro of manually doing it

    <pre>Option Explicit
    Sub Extracter()
    Dim wSource As Worksheet
    Dim wDest As Worksheet
    Dim sB As String
    Dim sD As String
    Dim lRowSource As Long
    Dim lRowDest As Long
    Set wSource = ActiveSheet
    With wSource
    lRowSource = .Cells(.Cells.Rows.Count, 4).End(xlUp).Row
    sB = "'" & .Name & "'!B$1:B$" & lRowSource
    sD = "'" & .Name & "'!$D$1:$D$" & lRowSource
    End With
    Set wDest = Worksheets.Add
    With wDest
    wSource.Rows("1:1").Copy _
    Destination:=.Range("A1")
    wSource.Range("D114").AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=.Range("D1"), _
    Unique:=True
    lRowDest = .Cells(.Cells.Rows.Count, 4).End(xlUp).Row
    .Range("b2:b2").FormulaArray = _
    "=IF(MIN(IF((" & sD & "=$D2)*(LEN(" & sB & _
    ")>0),ROW(" & sD & ")))=0,"""",INDEX(" & sB & _
    ",MIN(IF((" & sD & "=$D2)*(LEN(" & sB & _
    ")>0),ROW(" & sD & ")))))"
    .Range("B2").Copy .Range("B3:B" & lRowDest)
    .Range("B2").Copy .Range("E2:P" & lRowDest)
    .Range("B2").Copy .Range("A2:A" & lRowDest)
    .Range("B2").Copy .Range("C2:C" & lRowDest)
    wSource.Columns.Copy
    .Range("a1").PasteSpecial xlPasteFormats
    .Columns("Q:R").Delete
    .Cells.Copy
    .Range("A1").PasteSpecial xlPasteValues
    End With
    Application.CutCopyMode = False
    Set wSource = Nothing
    Set wDest = Nothing
    End Sub</pre>


  8. #8
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    fill in missing data (excel 2003)

    Thanks steve,
    I'll try the code you provided, but in the meantime i was trying your previous method (with array formula) on copies of my project .
    I. when you use adv filter, you can only copy it on the active sheet, correct and how did you copy it to other sheet
    2.Once in the newly copied sheet, every time i try to copy your array formula in cell A2 & past special it, by applying (ctr, shift , and enter) it will delete the content in cell A2.

    TIA,
    OCM

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: fill in missing data (excel 2003)

    1) You must start the advanced filter from the destination sheet, then specify the database range on the source sheet.
    2) Steve used advanced filter with cell D1 on the destination sheet as target range. This fills unique values in column D. The other columns are still blank. They will be populated with the formula. Since column A on the source sheet is blank, it'll be blank on the destination sheet too.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: fill in missing data (excel 2003)

    Hans answered your question but some clarification. The code uses the array method I posted earlier. It just "finalizes" it by removing the formulas.

    If you eliminate the line (or comment it out):
    .Range("A1").PasteSpecial xlPasteValues

    You should get the sheet using the method I posted earlier. As I mentioned, the code does the transformation exactly how I did it manually. I was "lazy" and let XL do most the "heavy lifting" in the code, having it use adv filter and also using the array formula rather than creating my own code to do this.

    Steve

  11. #11
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    fill in missing data (excel 2003)

    Thank to you and Hans and also for the clarifications. I'll apply your suggestions and post back if i have any question.

    Regards,
    OCM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •