Data Paste

[BRamsundar]

Hello All,

I have an interesting question that needs some answering.

I have a set of columns loaded with information. One of the columns has a set of information in it that relies on 5 columns. Here is an example:

Example 1:

Column B
AREA:80:FORT ERIE:_TOWN_UNKNOWN:BERTIE ST

Column E
80

Column H
FORT ERIE

Column I
(BLANK)

Column U
BERTIE

Column V
ST

Example 2:

Column B
AREA:80:FORT ERIE:_TOWN_SMALLVILLE:BLACK CREEK RD

Column E
80

Column H
FORT ERIE

Column I
SMALLVILLE

Column U
BLACK CREEK

Column V
RD

Attached is a spreadsheet of what it should look like.

Book2.xlsx

I need the process to be automated by a macro to scan the columns and automatically input that info. into Column B with the template:

Area:Column E:Column H:_TOWN_Column I:Column U Column V

[davesexcel]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[FDibbins]

Not sure if this is what you are after...
="AREA:"&E2&":"&H2&":_"&IF(I2="","TOWN_UNKNOWN",I2)&":"&U2&" "&V2

If not, please read what Dave said

[BRamsundar]

Hello,

This code will work for my applications:

="AREA:"&E182&":"&H182&":_"&IF(I182="","TOWN_UNKNOWN",I91)&":"&IF(U182="","STREET_UNKNOWN",U182)&" "&V182

Thanks for the help guys/gals

Regards,

B

[BRamsundar]

Hey FDibbins,

I've got a little problem that I didn't notice until just recently.

I wanted to put an "ELSE" and/or "ELSEIF" into the formula.

Attached is the file. If you look at ROW 92 you will noticed that there is actually a street associated with the address, but the code did not capture it. I want it to capture what's in AA (Street name) and AB (Street Type), ELSE "_STREET_UNKNOWN"

[FDibbins]

I dont think the file made it?

[BRamsundar]

Sorry,

I forgot the file. Here it is:

[FDibbins]

ok, you can use the method I used in my formula for all the cells, if you want to...

="AREA:"&E2&":"&H2&":_"&IF(I2="","TOWN_UNKNOWN",I2)&":"&U2&" "&V2

just extend the bolded part to the other references

[BRamsundar]

I don't quite get the 'extend the bolded part to other references'

[BRamsundar]

Here is the code I will be using:

="AREA:"&E5&":"&H5&":_"&IF(I5="","TOWN_UNKNOWN",I5)&":"&IF(U5="","_STREET_UNKNOWN",U5)&" "&V5

Look at Rows 5-15. There are streets associated with those addresses, the code is capturing it as _STREET_UNKNOWN. I want it to scan through Column U and Column AA, when it finds nothing it should put _STREET_UKNOWN

[FDibbins]

I meant that you use the same method I used with the IF() function for the rest of the references...

="AREA:"&E2&":"&H2&":_"&IF(I2="","TOWN_UNKNOWN",I2)&":"&IF(U2="","whatever the column name is_UNKNOWN",U2)&" "&IF(V2="","whatever the column name is_UNKNOWN",V2)