Find and Replace

[ronmurtaugh]

I am trying to expand my knowledge, please be patient. I get sheets from a variety of 3rd party vendors and their software. I am looking to make a sheet that I can copy and paste the information into the document. I figured out all of it, but description (building permit type). For instance, I want the "master sheet" to be able to find a variety of descriptors and return our internal standard descriptor. For instance, some people list fences, fencing, new fence, etc in their submitted sheet. I need it to return "FENCE". Than you in advance.

 

[etaf]

how many different descriptors do you have and how many to convert

 

[ronmurtaugh]

how many different descriptors do you have and how many to convert

Between four entities, probably 20.

 

[etaf]

can we see them - to see if there is something common that can be used to group
with this it will depend on the text
But to convert 20 to 4 may not be difficult
or a lookup table

BUT I Suspect i'm not following exactly and may not be able to help here
maybe some examples in a sample sheet may help

this may be over simplified

Book4
	A	B	C	D	E	F
1	list	return
2					ref table
3	new Fences	Fence			fences	Fence
4	windows	Window			fencing	Fence
5	door handle	Door			new fences	Fence
6	window sill	Window			windows	Window
7					window frame	Window
8					window sill	Window
9					Door	Door
10					Door handle	Door
11					Door frame	Door
12
Sheet1

Cell Formulas
Range	Formula
B3:B6	B3	=INDEX($F$3:$F$11,MATCH("*"&A3&"*",$E$3:$E$11,0))

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[Alex Blakenburg]

Since you have MS 365 this might work for you:

20240320 Reverse Lookup ronmurtaugh.xlsx
	A	B	C	D	E	F
1	list	return			Standard	Minimum Identifier
2
3	new Fences	Fence			Fence	Fenc
4	windows	Window			Window	Window
5	door handle	Door			Door	Door
6	window sill	Window
7	Fencing	Fence
XLookup

Cell Formulas
Range	Formula
B3:B7	B3	=XLOOKUP(1,--ISNUMBER(SEARCH($F$3:$F$5,A3)),$E$3:$E$5,"")

 

[ronmurtaugh]

Thanks in advance for your help! Here is a mini-sheet. I can have 200 permits at any time and the permit types can be spread out in the sheet in any cell.

Mr Excel Sample.xlsx
	A	B	C	D	E	F
1	TOWN A			DATALOAD SHEET
2	Building Permits				RETURN THIS
3	Address	Permit Type	FEE	Address	Permit Type	FEE
4	123 Main	Residential New Construction	250	123 Main	New Contruction	250
5	444 Jones St	Commercial Accessory Structure	100	444 Jones St	Addition	100
6	2563 Smith Cr	New Pool/Spa	50	2563 Smith Cr	Pool	50
7	524 Miller Ave	Residential New Construction	250	524 Miller Ave	New Contruction	250
8	536 Brown Terr	Residential New Construction	250	536 Brown Terr	New Contruction	250
9	558 Park Ave	Residential Alteration/Repair Addition	100	558 Park Ave	Addition	100
10	156 Boyd St	New Pool/Spa	50	156 Boyd St	Pool	50
11	626 Fred Street	Demolition	50	626 Fred Street	Wreck	50
12	563 Anystreet	Bathroom Remodel	100	563 Anystreet	Bathroom Remodel	100
13	5858 Smithville St	Basement Remodel/Finish	100	5858 Smithville St	Basement Fin	100
14	2525 Zoo Ln	Commercial New Construction	500	2525 Zoo Ln	New Contruction
Sheet1

 

[Alex Blakenburg]

I don't understand this comment.

the permit types can be spread out in the sheet in any cell.

I also don't understand where the $ 500 comes from on the last New Construction which was spelt Contruction (Missing the "s")

20240320 Reverse Lookup ronmurtaugh.xlsx
	A	B	C	D	E	F	G	H	I	J
1	TOWN A			DATALOAD SHEET				Lookup
2	Building Permits				RETURN THIS			Permit Type	Used Alternatives	FEE
3	Address	Permit Type	FEE	Address	Permit Type	FEE		New Construction		250
4	123 Main	Residential New Construction	250	123 Main	New Construction	250		Addition	Commercial Accessory	100
5	444 Jones St	Commercial Accessory Structure	100	444 Jones St	Addition	100		Pool		50
6	2563 Smith Cr	New Pool/Spa	50	2563 Smith Cr	Pool	50		New Contruction		250
7	524 Miller Ave	Residential New Construction	250	524 Miller Ave	New Construction	250		Wreck	Demolition	50
8	536 Brown Terr	Residential New Construction	250	536 Brown Terr	New Construction	250		Bathroom Remodel		100
9	558 Park Ave	Residential Alteration/Repair Addition	100	558 Park Ave	Addition	100		Basement Fin	Basement	100
10	156 Boyd St	New Pool/Spa	50	156 Boyd St	Pool	50		Addition	Annex	100
11	626 Fred Street	Demolition	50	626 Fred Street	Wreck	50
12	563 Anystreet	Bathroom Remodel	100	563 Anystreet	Bathroom Remodel	100
13	5858 Smithville St	Basement Remodel/Finish	100	5858 Smithville St	Basement Fin	100
14	2525 Zoo Ln	Commercial New Construction	500	2525 Zoo Ln	New Construction	250
15		Residential Annex			Addition	100
New Data

Cell Formulas
Range	Formula
E4:E15	E4	=XLOOKUP(1,ISNUMBER(SEARCH($H$3:$H$10,$B4))*($H$3:$H$10<>""),$H$3:$H$10,XLOOKUP(1,ISNUMBER(SEARCH($I$3:$I$10,$B4))*($I$3:$I$10<>""),$H$3:$H$10,""))
F4:F15	F4	=XLOOKUP(1,ISNUMBER(SEARCH($H$3:$H$10,$B4))*($H$3:$H$10<>""),$J$3:$J$10,XLOOKUP(1,ISNUMBER(SEARCH($I$3:$I$10,$B4))*($I$3:$I$10<>""),$J$3:$J$10,""))

 

[ronmurtaugh]

You can disregard the permit cost. I wanted to just show a sample sheet of our building permit sheet. The permit type is the only area that I need to return a standardized entry.

 

[Alex Blakenburg]

So do you have something that works for you in what had been provided ?