MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Going loopy with loops

  Asked By: Ira    Date: Dec 17    Category: MS Office    Views: 1481

I am trying to sort out a huge data file which is a mess. There is
not one unique column to go on, so I have to use a mixture of IP
address and name to get a tab per person.

I have sorted it according to IP address first, so now I have a wkb
with a tab per unique IP address. Some sheets contain multiple names
still though, so I have to go through again and separate out
according to name, and re-name each tab to the persons name. There
are various spellings of the names though so I will end up with an
exception report of records that don't fit - this is fine.

I'm looking for advice on how to approach the sorting with each wks.
One case might be the same name on 10 sheets in a row (I need to
combine these to one sheet), another case might be 4 different names
within one sheet that all need to be separated out to threr own
sheet. I hope this makes some sense.

I can do a 'For each' loop for each of these scenarios then re-name
the tabs at the end, although the first scenario needs to hold the
name in a variable and keep looking through the sheets, but the name
might be spelt differently too in , say the third sheet.



3 Answers Found

Answer #1    Answered By: Vinit Online     Answered On: Dec 17

Can I take it that you're only doing this job once? If so, it will almost
certainly be quicker to do it by hand than by writing code.

One facility that probably will help is auto-filter. If you have a sheet
that you need to split up, you can auto-filter for the first  choice, then
create a new sheet  for that selection and copy the visible rows across.
Then filter for the next choice and repeat.

The nice thing about auto-filter is that the little drop-down shows you all
unique values for the column, so you know in advance how many you're dealing
with. You can also spot misspellings from this drop-down with ease, then
filter on the bad one and change it to good, then filter on the proper one
and deal with them all, together.

Answer #2    Answered By: Jake Williams     Answered On: Dec 17

I'm automating the process to be used once a
month or so. I coded it to use autofilter the first  time around. I
guess I can use it again now on each sheet  to separate  them out
again. For the other scenario  where the same name  appears on a few
sheets in a row  - Are there any suggestions on how to code this
efficiently? I need to combine them to one sheet.

Answer #3    Answered By: Muriel Dunn     Answered On: Dec 17

No I don't really have any suggestions for when the same name  appears on a
few sheets  in a row. The problem is that it is probably too late to do a
lot - it would have been nice to identify them as the same name before
sending them to the different sheets.

My feeling is that you'd be better off keeping everything in the one place
until the analysis is finished, then split it up based on that analysis.

Didn't find what you were looking for? Find more on Going loopy with loops Or get search suggestion and latest updates.