Excel help requested please

[SpeckledJim]

Hello,
I'm hoping an Excel guru can help me with this please?

I have a spreadsheet of 200,000 products from an ecommerce shop.

In the column 'product name' some of the product names include the phrase 'NLA' to denote 'no longer available'

Wherever NLA appears in a product name I want to update another column called 'product not visible' with a numeral '1'

Any and all help gratefully received

[Sinatra21]

=if(A1="NLA",1," "). Where A1 is the first row and column with data you are looking at, change accordingly

[paulrockliffe]

Sinatra21 said:

=if(A1="NLA",1," "). Where A1 is the first row and column with data you are looking at, change accordingly

This won't work if you take 'include' to mean there are other things in the cell too.

[SpeckledJim]

paulrockliffe said:

Sinatra21 said:

=if(A1="NLA",1," "). Where A1 is the first row and column with data you are looking at, change accordingly

This won't work if you take 'include' to mean there are other things in the cell too.

There are, sadly. The NLA isn't in a consistent place/position either.

I can search the column to find the instances of NLA, but I don't know how to automate the change to the 'product not visible' column, and there are just too many to do manually.

Thanks both for your help.

[Sinatra21]

Then we need to know if NLA is in the same position in all of the cells (i.e first 3 or last 3 digits or mixed in randomly. Is the data in those cells separated by spaces, commas etc?

A screen shot would help

[toon10]

If column A1 = header "Product Name" and column B1 = header for check...

Enter:

=IF(ISERROR(SEARCH("*NLA*",A2,1)),"","1")

In column B2 and drag down.

Edited by toon10 on Friday 28th June 10:55

Edited by toon10 on Friday 28th June 10:56

[mmm-five]

Sinatra21 said:

=if(A1="NLA",1," "). Where A1 is the first row and column with data you are looking at, change accordingly

Maybe change it slightly to mean 'contains NLA' by adding an asterix before/after the NLA...

=if(A1="*NLA*",1," ")

[SpeckledJim]

The PH WIZARDS deliver once again.

That's brilliant, sincere thanks everyone!

[goes round the room high-fiving and kissing people on the forehead}

MWAH!

[SpeckledJim]

toon10 said:

If column A1 = header "Product Name" and column B1 = header for check...

Enter:

=IF(ISERROR(SEARCH("*NLA*",A2,1)),"","1")

In column B2 and drag down.

Edited by toon10 on Friday 28th June 10:55

Edited by toon10 on Friday 28th June 10:56

Sorry, this worked earlier on and I'm trying to repeat it on a different spreadsheet, but your edited version doesn't seem to work. Could you repeat the first version please?

[c6r]

equivalently to above you could use isnumber and find instead:

=IF(ISNUMBER(FIND("NLA",A1)),1,0)

[SpeckledJim]

c6r said:

equivalently to above you could use isnumber and find instead:

=IF(ISNUMBER(FIND("NLA",A1)),1,0)

Sorry, I might be doing it wrong.

If column A says : Renault 832140000 ARM SUSP RHF NLA
And I put that formula into the adjacent column B, it is returning a 0.

Can you put me straight please?

[Planet Claire]

SpeckledJim said:

Sorry, I might be doing it wrong.

If column A says : Renault 832140000 ARM SUSP RHF NLA
And I put that formula into the adjacent column B, it is returning a 0.

Can you put me straight please?

It works for me - are you sure you are referencing the correct cell in column A?

[SpeckledJim]

Planet Claire said:

SpeckledJim said:

Sorry, I might be doing it wrong.

If column A says : Renault 832140000 ARM SUSP RHF NLA
And I put that formula into the adjacent column B, it is returning a 0.

Can you put me straight please?

It works for me - are you sure you are referencing the correct cell in column A?

And with that I sink ever further into the PH Brains Trust's debt.

All sorted, thank you!

[toon10]

SpeckledJim said:

And with that I sink ever further into the PH Brains Trust's debt.

All sorted, thank you!

Sorry, just picked this up now, glad you're all sorted.