1. S

    Sort by cities

    Hi all, Is it possible with Excel VBA to sort data according to the text in record, in this case city, above Whitsett Ave (N. Hollywood) Victory Blvd (N. Hollywood) Kenneth Rd (Burbank) Alameda Ave (Burbank) Kenneth Rd (Burbank) Central Ave (Glendale) Central Ave (Glendale) To get: Alameda...
  2. M

    Rows with duplicate values merge and delete duplicate keep unique value in row

    I am trying to get my table to look like table 2. Any help would be appreciated. <tbody> Name Address Insured Address Policy Policy2 John Doe 123 ABC ST 123 ABC ST 8888A John Doe 123 ABC ST 123 ABC ST 9999B MIKE A 345 CCC ST 345 CCC ST 5555C MIKE A 345 CCC ST 345 CCC ST...
  3. E

    problem with data gathering from online website

    i want get data from web site with following code: <div class="box6 h80"><table><tbody><tr><td>range day</td><td id="d06" class="">11,670</td><td id="d07" class="">11,300</td></tr><tr><td>price</td><td id="PRange1">12,191</td><td id="PRange2">11,031</td></tr><tr><td>week...
  4. L

    correct case format

    Hi! I need to clean up a excel file (2016) with approximatley 2000 records where many of the names and addresses are all caps or all lower case. Is there a forumula I can use to identify these records and change to proper case formating (JOE SMITH, 123 main ave. change to Joe Smith, 123 Main...
  5. N

    Move totals in a pivot table

    I have a pivot table where I put the value variables in the rows. The row definition looks like this: Ʃ Values Team Team is a column with team names. The defined values in the example below are 'Ave structures per bottle' and 'Ave structures per operator' The result is something like this...
  6. D

    Delete Duplicate Rows.

    Hello I have a worksheet with two columns: Address and City. Column A contains duplicate data all over, i'm looking for a macro that will look in column A and if it finds DUPLICATE then it should DELETE BOTH DUPLICATE ROWS. As you can see from the sample data below, BOT ROWS for address " 1181...
  7. T

    Macro to Split After "Apartment [0-9]"?

    Hello, I receive address files from clients that are rarely formatted properly and usually have all their data pasted in 1 column. (See example below) I've made a macro that adds "|" at the end of Street, Ave, Road, etc and then splits into different columns but I can't figure out how to split...
  8. C

    Text Function Formulas - Find/Substitute/Replace

    Hello, I have Street Names in Column A. They are all different and may contain apartment or unit numbers at the end. I would like to search the text in the cell from RIGHT to LEFT to find the first instance of "ST" or "AVE" or "RD". I would then like to remove any text to the right from that...
  9. R

    Aggregate and Concatenate

    Hello, I have a dataset of property owners, some of whom own multiple properties. Whenever I add them to my database, I need to group up all of the property each owner has into one cell next to their name. Otherwise, I'll have 4 contacts with the same name just different properties. I have...
  10. M

    LookUp/Match/Index Not Working

    I have the following formula: =(LOOKUP(B3,January!D:D,January!J:J)) B3 = "Agent 3" The Table i am trying to look form is the following: <tbody> <tbody> A B C D E F G H I J K Location CSM Manager Agent Conformance % Adherence % Inbound Calls Ave Inb Talk Ave Inb Hold Avg Inb Wrap Inb AHT...
  11. M

    Totaling Heat Types by Towns

    Hello Everyone, I have a large list of addresses that have heat system types associated with them. I want to be able to get it into a format where it is just the town name and a total number of each heat type. I would normally do this manually but there are over 1 million addresses in this...
  12. T

    Formula to Separate City from Address

    I have a list that looks like: <tbody> Address 1820 E River Rd Ste 230Tucson 7550 E Mcdonald Dr Ste AScottsdale 442 Kaleimamahu StHonolulu 6 Larch Ave Ste 398Wilmington 85 Willow StNew Haven 64 Thompson St Ste A105East Haven PO BOX 2590416 Slater RoadNew Britain 7059 Blair Rd...
  13. V

    Adding based on groups in excel

    Hi guys I want to add the sales number if the id is equal example: I want add 901 and 2994 if id is same in this case id is 40317 <colgroup><col><col span="6"></colgroup><tbody> 539 8th Ave 40317 40317 C N Y 901 539 8th Ave 40317 40317 E N Y 2994.5 </tbody>
  14. C

    Golf league handicapping conundrum

    I built a golf handicapping sheet for my league of 30 players, each player has a row for score entries for 100+ rounds. Each week I enter into row A Frank's score, from A1 across. If Frank doesn't play that week, the entry is "0". Row B contains formulas in each cell from B1 across that look...
  15. T

    VBA Translate Column Data into Rows

    I have a column of information and would like to find a way to translate it into rows using VBA. Every 3-7 rows in my existing column has a cell "New Record". I'd like the information below that cell to be laid out in a new row. Currently: <tbody> New Record 4756 John Doe 123 Street...
  16. L

    nested substitute()

    Hi I want to get rid of all numbers in a text. So I thought of used nested substitute() function and nested 10 times go get ride of 0-9. I have a column with house address and i want to keep street address not the number and then autofill this function. Do you think there is better idea to do...

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back