check a value in a existing list, if exist increase value

**lucholac** · 10-23-2017, 09:24 AM

I need to check a value in a existing list, if the value doesn't exist give extension _00 but if exist increase value by 1 (i.e. _03) in excel.
Example
1.- Value to check from the list below WG200-DWG300
2.- Value to check from the list below AXT-TST400

ATS-ADS100_01
ATS-ADS100_02
ATS-ADS100_03
WG200-DWG300_00
WG200-DWG300_01
WG200-DWG300_02
WG200-DWG300_03
WG200-DWG300_04
WG200-DWG300_05 (1.- this should be the new value added to the list)
AXT-TST400_00 (2.- this should be the new value added to the list)

Thanking you in advance.
Luis

**shivya** · 10-23-2017, 09:30 AM

I couldn't understand your second condition. Please attach your excel sheet.

**63falcondude** · 10-23-2017, 09:35 AM

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

**lucholac** · 10-23-2017, 01:08 PM

Sorry, here is a simplify version.
New Document Number TO BE ADDED WG200-DWG300_00 This Number need to be check on the list below, if it does EXISTS, it needs to be incremented by 1 ("_01") and checked and until it does not exist and added at the end of the list as shown in Red.

List of existing documents
ATS-ADS100_00
ATS-ADS100_01
ATS-ADS100_02
ATS-ADS100_03
WG200-DWG300_00
WG200-DWG300_01
WG200-DWG300_02
WG200-DWG300_03
WG200-DWG300_04
WG200-DWG300_05

Thanks in advance.

Luis

**Glenn Kennedy** · 10-23-2017, 01:16 PM

With your list commencing in A2, use this in B2, copied down.

=A2&"_"&TEXT(COUNTIF($A$2:A2,A2)-1,"00")

If this is not what you need....

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**lucholac** · 10-23-2017, 08:33 PM

Thanks a lot Glenn
I have not been able to attached a file, so I have screened capture of the file.
may be I can email you the file.

check a value in a existing list.xlsx (the file)
Microsoft Excel - check a value in a existing list.jpg (the image)

Thanks in advance for your help.

Luis

**Glenn Kennedy** · 10-24-2017, 03:39 AM

If the new values are always being added to C2, then this is not possible using a formula. Any formula capturing the value in C2 will always reflect the balue in C2. Change C2, and all vlues linked to the cell change, too. If the next values were to be added in c2, c3, c4, etc, it would be easy.

You will require VBA to do as you wish. If that is what you want, tell me on this thread and I'll get one of the VBA people here to take a look (I'm hopeless with VBA).

**lucholac** · 10-24-2017, 08:13 AM

Thanks Glenn
It looks like I need VBA because I will be entering a Value on C2 and compare it to the list on column B. If it doesn't exist add it to the end of the list. If it exits incremented by 1 and add it to the end of the list and then sort it.
So, could you get a VBA person to help me with this?
Thanking you all in advance.

Luis

**xlnitwit** · 10-24-2017, 08:35 AM

Hi,

Perhaps this

Please Login or Register  to view this content.

To add the code, right click the worksheet tab, choose View Code and then paste that in.

**lucholac** · 10-24-2017, 11:36 AM

Thanks a lot that works very well, but would it be possible to display the result an cell B2?
Sorry to such a pest.

check a value in a existing list.xlsm

Regards

Luis

**xlnitwit** · 10-24-2017, 11:43 AM

Yes- you need only change this part

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

check a value in a existing list, if exist increase value

LinkBack

Thread Tools

Rate This Thread

Display

check a value in a existing list, if exist increase value

Re: check a value in a existing list, if exist increase value

Re: check a value in a existing list, if exist increase value

Re: check a value in a existing list, if exist increase value

Re: check a value in a existing list, if exist increase value

Re: check a value in a existing list, if exist increase value

Re: check a value in a existing list, if exist increase value

Re: check a value in a existing list, if exist increase value

Re: check a value in a existing list, if exist increase value

Re: check a value in a existing list, if exist increase value

Re: check a value in a existing list, if exist increase value

Thread Information

Users Browsing this Thread

Similar Threads

check a value in a existing list, if exist increase value

[SOLVED] Check for existing File name, open if exist or open new workbook if not

Check if sheet exist based on drop down list

[SOLVED] Check First IF Sheet Is Exist With The Name Of Cell K7 If exist Then run the code

open existing file if exist, else create it.

[SOLVED] Check data from existing list

Check If Folders in a List Exist

Bookmarks

Bookmarks

Posting Permissions