=IF(C1=""," ","ImportID-01")

[Guest]

Hey all,
As you can see the formula will fill a column next to any fields that are
not blank with ImportID-01. This works really swell, but I would like to also
increase the 01 by 1 so that it looks like this:

ImportID-01 Ringo

ImportID-02 Paul
ImportID-03 John


ImportID-04 George

Any ideas??

Thanks, you guys have never failed me! I appreciate it so much..
Bryce

 

[Guest]

This requires that row 1 be blank. Change the $A$1:A1 portion to reference
the column whre you place the formula

=IF(C2="","","ImportID-"&TEXT(SUMPRODUCT(--ISNUMBER(SEARCH("Import",$A$1:A1)))+1,"00"))

 

[Guest]

Almost there Duke!

It is placing 0s in the field next to where the data is. So it is finding
the correct fields, but it isn't placing ImportID-01 and then increasing by
1. Thanks so far!

 

[Guest]

Did you change the $A$1:A1 reference to the column of interest??

 

[Guest]

Yes. In my case your formula would look like this:
=IF(M2="","
","ImportId-"&TEXT(SUMPRODUCT(--ISNUMBER(SEARCH("Import",$K$1:K1)))+1,"00"))

BTW This is what the data file really looks like:

K L
M
Header Header
Blank
(Blank until function puts info in) Old Data
802-888-8777

 

[Guest]

Your formula works perfectly for me. I'm using Excel 2007, but can't see any
reason the version would cause a difference

 

[Guest]

I am using 2003. I can't find any reason for the problem to be related to the
version. Either. Sigh...

 

[Guest]

I'm wondering if you're making this more difficult than it needs to be. Try
this in cell M2:

=IF(K2="","","ImportID-"&TEXT(COUNTIF($K$2:K2,"<>"&""),"00"))

Copy down as needed.

HTH,
Elkar

 

[Guest]

Nifty, but I haven't noticed it increasing 01 to 02, 03, etc...

 

[Harlan Grove]

=IF(M2="","","ImportId-"
&TEXT(SUMPRODUCT(--ISNUMBER(SEARCH("Import",$K$1:K1)))+1,"00"))

....

Maybe try

=TEXT(SUMPRODUCT((TRIM(M2)<>"")*(TRIM(M
$2:M2)<>"")),"""ImportID-""00;;")

 

[Guest]

Thanks , but I got the message:

The formula you typed contains an error..

Did I have to change M to the empty cells in L, in the second part?

 

[Harlan Grove]

Thanks , but I got the message:

The formula you typed contains an error..

Awkward word wrapping. I'll fix it below.

Did I have to change M to the empty cells in L, in the second part?

....

No. If the names are in column M, then that's the only column to which the
formula would need to refer.

=TEXT(SUMPRODUCT((TRIM(M2)<>"")*(TRIM(M$2:M2)<>"")),
"""ImportID-""00;;")

 

[Guest]

Thanks so much! It worked like a charm.
Thanks everyone for your help. It renewed my faith in the power of the good
ol' Arapnet.

 

[Guest]

All is was well when suddenly...

I noticed that I will need the number to be counting the cell number, for
example

ImportID-01 Joe
Kathy
ImportID-03 Sam
ImportID-04 Lewis

ImportID-06 Trish

Any ideas? Thanks again very, very much!

 

[Harlan Grove]

I noticed that I will need the number to be counting the cell number, for
example

ImportID-01 Joe
Kathy
ImportID-03 Sam
ImportID-04 Lewis

ImportID-06 Trish

Any ideas? Thanks again very, very much!

....

Simpler. If the topmost name were in cell M2,

=TEXT((TRIM(M2)<>"")*ROWS(M$2:M2),"""ImportID-""00;;")

 

[Guest]

Nice work Harlan! It worked just right with text such as ImportID-

How about if it were just a number and I would still like to have it grow by
each row (if not blank)..

Such as:

1 Lucy

3 Trina

 

[Harlan Grove]

How about if it were just a number and I would still like to
have it grow by each row (if not blank)..

Such as:

1 Lucy

3 Trina

....

Give someone a fish, and you feed them for a day. Teach them to fish,
and you feed them for life. Time for you to fish.

What do you think you'd need to remove from the last formula I gave to
produce this new behavior? Try experimenting with the formula.