fill in missing data (excel 2003)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

(Edited by sdckapr on 28-Nov-07 14:58. Oops forgot about column C)

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

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("D1:D14").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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote