Posts by Jaffey

My mistake. I thought I had attached a spreadsheet.Thanks!

I'm trying to write a formula that can subtract one column from another while taking into account multiple criteria (client name and date) since the rows in the columns do not line up. Essentially, I am trying to compare the Cumulative Sales amount from the end of last month to the Cumulative Sales amount from the same report pulled today for each client. I have attached a sample spreadsheet as well to better illustrate. Thank you!
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[TD="width: 315, colspan: 4"][SIZE=8px]END OF LAST MONTH[/SIZE][/TD]
[TD="width: 27"] [/TD]
[TD="width: 313, colspan: 4"][SIZE=8px]MONTH-TO-DATE[/SIZE][/TD]
[TD="width: 30"] [/TD]
[TD="width: 92"] [/TD]
[TD="width: 26"] [/TD]
[TD="width: 190"] [/TD]

[SIZE=8px]Cumulative[/SIZE]

[SIZE=8px]Cumulative[/SIZE]

[SIZE=8px]Client[/SIZE]

[SIZE=8px]Month[/SIZE]

[SIZE=8px]Total Value $[/SIZE]

[SIZE=8px]$ Sales[/SIZE]

[SIZE=8px]Client[/SIZE]

[SIZE=8px]Month[/SIZE]

[SIZE=8px]Total Value $[/SIZE]

[SIZE=8px]$ Sales[/SIZE]

[SIZE=8px]MTD Sales[/SIZE]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Dec-08[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$467,822.77[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$1,509.52[/SIZE][/TD]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Feb-08[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

[SIZE=8px]N/A[/SIZE]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Jan-09[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$8,468,384.04[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$176,220.80[/SIZE][/TD]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Mar-08[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

[SIZE=8px]N/A[/SIZE]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Feb-09[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$348,637.28[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$37,586.88[/SIZE][/TD]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Apr-08[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

[SIZE=8px]N/A[/SIZE]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Mar-09[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$1,420,134.05[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$52,156.03[/SIZE][/TD]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]May-08[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

[SIZE=8px]N/A[/SIZE]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Apr-09[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$4,747,310.52[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$153,970.17[/SIZE][/TD]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Jun-08[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

[SIZE=8px]N/A[/SIZE]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]May-09[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$4,855,414.00[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$244,408.09[/SIZE][/TD]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Jul-08[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

[SIZE=8px]N/A[/SIZE]

[SIZE=8px]Company1[/SIZE]

[TD="align: left"] [TABLE="cellpadding: 0, cellspacing: 0"]

[TD="width: 43, align: right"][SIZE=8px]Jun-09[/SIZE][/TD]

[/TABLE]
[/TD]
[TD="align: right"][SIZE=8px]$2,278,476.57[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$91,687.02[/SIZE][/TD]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Aug-08[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

[SIZE=8px]N/A[/SIZE]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=10px]Jul-09[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$7,586,268.53[/SIZE][/TD]
[TD="align: right"][SIZE=10px]$343,676.64[/SIZE][/TD]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Sep-08[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

[SIZE=8px]N/A[/SIZE]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Aug-09[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$6,491,043.02[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$292,106.71[/SIZE][/TD]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Oct-08[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

[SIZE=8px]N/A[/SIZE]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Sep-09[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$11,198,413.23[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$335,428.04[/SIZE][/TD]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Nov-08[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

[SIZE=8px]N/A[/SIZE]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Oct-09[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$7,696,023.34[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$339,896.41[/SIZE][/TD]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Dec-08[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$467,822.77[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$1,509.52[/SIZE][/TD]

[SIZE=8px]$0.00[/SIZE]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Nov-09[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$7,289,044.85[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$353,909.07[/SIZE][/TD]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Jan-09[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$8,468,384.04[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$176,220.80[/SIZE][/TD]

[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Dec-09[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$10,475,084.15[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$337,676.23[/SIZE][/TD]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Feb-09[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$348,637.28[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$37,586.88[/SIZE][/TD]

[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Jan-10[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$10,874,495.91[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$494,469.19[/SIZE][/TD]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Mar-09[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$1,420,134.05[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$52,156.03[/SIZE][/TD]

[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Feb-10[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$10,815,212.87[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$407,795.75[/SIZE][/TD]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Apr-09[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$4,747,310.52[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$153,970.17[/SIZE][/TD]

[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Mar-10[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$19,726,852.92[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$574,868.66[/SIZE][/TD]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]May-09[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$4,855,414.00[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$244,408.09[/SIZE][/TD]

[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

[SIZE=8px]Need formula for "MTD Sales"[/SIZE]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Apr-10[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$11,612,756.75[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$464,359.96[/SIZE][/TD]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Jun-09[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$2,278,476.57[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$91,687.02[/SIZE][/TD]

[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

[SIZE=8px]that subtracts col "D" from col "I"[/SIZE]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]May-10[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$19,022,577.83[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$665,844.16[/SIZE][/TD]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=10px]Jul-09[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$7,586,268.53[/SIZE][/TD]
[TD="align: right"][SIZE=10px]$343,701.64[/SIZE][/TD]

[TD="align: right"][SIZE=10px]$25.00[/SIZE][/TD]

[SIZE=8px]to calculate this month's sold [/SIZE]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Jun-10[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$11,402,868.39[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$395,700.68[/SIZE][/TD]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Aug-09[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$6,491,043.02[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$292,106.71[/SIZE][/TD]

[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

[SIZE=8px]amount while matching client[/SIZE]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Jul-10[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$11,060,440.15[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$328,009.04[/SIZE][/TD]

[SIZE=8px]Company1[/SIZE]

[TD="align: right"][SIZE=8px]Sep-09[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$11,198,413.23[/SIZE][/TD]
[TD="align: right"][SIZE=8px]$335,428.04[/SIZE][/TD]

[TD="align: right"][SIZE=8px]$0.00[/SIZE][/TD]

[SIZE=8px]and month[/SIZE]

[/TABLE]

That is outstanding! Thank you so much!!!

I have a column of data containing dollar amounts, as well as a record count, within brackets, in each cell. (see below)

I can extract the dollar amounts and record counts individually with formulas easily enough but I haven't been able to figure out a formula to sum the entire column. I suspect an array formula is the ticket but I've had no luck figuring it out. Any suggestions greatly appreciated!

I have attached a sample file with the below example as well. Thank you

[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[TD="width: 55"] Month[/TD]
[TD="width: 228"]Total Assigned[/TD]
[TD="width: 184"][SIZE=9px]=VALUE(TRIM(LEFT(C3,FIND("(",C3)-1)))[/SIZE][/TD]
[TD="width: 663, colspan: 2"][SIZE=9px]=VALUE(LEFT(MID(C8,FIND("(",C8)+1,30),LEN(MID(C8,FIND("(",C8)+1,30))-1))[/SIZE][/TD]

May-17

$10,424,966.01 (4077)

10,424,966.01

4,077

Jun-17

$11,518,944.31 (2441)

11,518,944.31

2,441

Jul-17

$13,979,761.57 (5886)

13,979,761.57

5,886

Sum of Assigned Amounts prior to Sep17

Aug-17

$14,471,740.49 (4816)

14,471,740.49

4,816

50,395,412.38 =SUMIFS(D:D,B:B,"<"&B6)

Sep-17

$13,162,785.18 (4866)

13,162,785.18

4,866

'- need a formula to do this without using the helper columns

Oct-17

$13,564,501.05 (4219)

13,564,501.05

4,219

Nov-17

$14,502,575.36 (5642)

14,502,575.36

5,642

Dec-17

$16,383,880.72 (5586)

16,383,880.72

5,586

Jan-18

$21,483,088.34 (6129)

21,483,088.34

6,129

Feb-18

$16,041,918.66 (4454)

16,041,918.66

4,454

Mar-18

$27,936,497.70 (6845)

27,936,497.70

6,845

Apr-18

$3,776,844.67 (1033)

3,776,844.67

1,033

$177,247,504.06 (55994)

177,247,504.06

55,994

[SIZE=11px]=??? need formulas to sum amount & count[/SIZE]

[/TABLE]

I receive half a dozen text files each day containing 38 to 51 columns of data but no column labels. They are csv files saved with a .txt extension so excel will open them with the import text wizard. The column labels are provided later via email, although sometimes not at all.

Columns 1-38 never change and are not an issue. Columns 39 - 51, however, can change in both content and order.

My goal is to develop a script that can look at the data in the columns and either assign the appropriate column label or, ideally, move the data into the appropriate column in the 'master' data table (“Table2”).

The variable columns (39-51) can be any of the following;

2. Account Number - (1 to 16 digits, always arrives in text format, never blank)
10. Home Phone# - (messy, often blank, may contains alpha chars)
38. Sub Product ID - (3 to 7 alpha characters, can be blank)
Append Score 7 Value - (2 digits, often blank)
Last Worked Date - (never blank, always in 08/02/2017 4:08:23 PM format or “0000-00-00 00:00:00”)
Last Worked By - (often blank, 9 alpha chars, 1^st 3 chars always the same)
1st Additional phone - (messy, often blank and/or contains alpha chars as well)
2nd Additional phone - (messy, often blank and/or contains alpha chars as well)
Email Address 1 - (often blank else a typically formatted email address)
Email Address 2 - (often blank else a typically formatted email address)
17. Assigned Balance - (never blank, always in proper number format with 2 decimals) This column also has the distinction of always being the last column when it is present.

I have attached a sample file containing the raw data as it is received on the first tab and with the headers on the 2^nd tab. I know this is a big ask but if anyone can at least provide some suggestions for how to approach it, I would greatly appreciate it. Basically, I’m looking for anything that can tell me ‘this column contains phone numbers” vs “this column contains dates and this one is the balance because it’s a number with 2 digits and never blank” etc. Any suggestions appreciated!

Thank you PCI for that very useful macro! I notice, however, when I run it against the sample records in my attached file it adds approximately 800 new columns to the table. This seems to be because it is adding a new column for each parsed note in each record.

For example, the first cell in the first row contains 27 notes bounded by "UTC NOTE" and "ENDNOTE". So the macro parses those 27 notes into 27 new columns. Then, when it processes the 43 notes in the first cell of the 2nd row, instead of using the blank cells in the newly created 27 columns, it parses them into 43 new columns starting from column 28.

Would it be possible to start from the first column each time it begins parsing a new record? I apologize if I am not describing this properly but I don't know how else to describe it.

Thank you for your response PCI! My goal is to extract the entire string between DATE and ENDNOTE. Alternately, "UTC NOTE:" could be used as the first goal post as it is less likely to generate false positives as "DATE" could easily be entered in a note by a user whereas UTC NOTE is less likely to be entered.

So from my original example above, the desired output should look like this:

2017-08-23 15:00:55 UTC NOTE:
forgot to go over the Consent letter with the customer since he has no email address.
Please read out the consent to the client or ask for an email address that we can forward it to.
WARNING:
Customer is little bit upset with his situation and may be rude.

I have attached a sample spreadsheet with some real notes as well as columns for each keyword I am extracting. In the final column, I have combined them all using one long formula as shown below to produce a summary report of sorts. The customer service manager can then review the extracted comments. Eventually, I would like to replace this formula with an index/match approach using tables where additional keywords can be added easily but first I need to figure out how to extract the full string properly from between the delimiters I am using.

=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(MID([@Notes],SEARCH("upset",[@Notes])-20,(SEARCH("ENDNOTE",[@Notes],SEARCH("upset",[@Notes]))-SEARCH("upset",[@Notes])+20)),MID([@Notes],SEARCH("frustrated",[@Notes])-20,(SEARCH("ENDNOTE",[@Notes],SEARCH("frustrated",[@Notes]))-SEARCH("frustrated",[@Notes])+20))),MID([@Notes],SEARCH("complain",[@Notes])-20,(SEARCH("ENDNOTE",[@Notes],SEARCH("complain",[@Notes]))-SEARCH("complain",[@Notes])+20))),MID([@Notes],SEARCH("escalate",[@Notes])-20,(SEARCH("ENDNOTE",[@Notes],SEARCH("escalate",[@Notes]))-SEARCH("escalate",[@Notes])+20))),MID([@Notes],SEARCH("misunder",[@Notes])-20,(SEARCH("ENDNOTE",[@Notes],SEARCH("misunder",[@Notes]))-SEARCH("misunder",[@Notes])+20))),MID([@Notes],SEARCH("not happy",[@Notes])-20,(SEARCH("ENDNOTE",[@Notes],SEARCH("not happy",[@Notes]))-SEARCH("not happy",[@Notes])+20))),MID([@Notes],SEARCH("wrong",[@Notes])-20,(SEARCH("ENDNOTE",[@Notes],SEARCH("wrong",[@Notes]))-SEARCH("wrong",[@Notes])+20))),MID([@Notes],SEARCH("confused",[@Notes])-20,(SEARCH("ENDNOTE",[@Notes],SEARCH("confused",[@Notes]))-SEARCH("confused",[@Notes])+20))),"")

Greetings,

I have a table with a "Notes" column where multiple notes from users appear inside a single cell. Each notes starts with DATE and ends with ENDNOTE.

Example of a note within a cell:

DATE 2017-08-23 15:00:55 UTC NOTE:
forgot to go over the Consent letter with the customer since he has no email address.
Please read out the consent to the client or ask for an email address that we can forward it to.
WARNING:
Customer is little bit upset with his situation and may be rude. ENDNOTE\\

My goal is a formula, or series of formulas, that looks for users who have had unsatisfactory experiences by scanning for keywords, such as "upset" or "frustrated".
I have the following formula that returns the first instance of "upset" plus the rest of the string until ENDNOTE plus 20 chars preceding it.

=MID([@Notes],SEARCH("upset",[@Notes])-20,(SEARCH("ENDNOTE",[@Notes],SEARCH("upset",[@Notes]))-SEARCH("upset",[@Notes])+20))

My goal is a formula that extracts the entire string between DATE and ENDNOTE if "upset" and/or "frustrated" etc. appears between them.

I actually have about a dozen keywords and phrases to search for so I would be very open to a solution that used a =index(match( sort of approach using a helper table containing the keywords.

Thanks in advance!
Jeff

Re: extract customer name and address from huge data dump in every cell

A little more info: In column A, each cell has thousands of rows filled with OHBA and Member Directory repeated over and over, a small portion of which is shown here. The company name also appears many times. In a few places, you will see everything come together neatly where the company name and address, followed by Contact Info and the contact's name, phone, email and/or website appears. Ideally, each element would be extracted to a separate column but for now I would be content just to get this block of data out.

EXAMPLE 1

OHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory
Alterra Group of CompaniesOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory
OHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory
Alterra Group of CompaniesAddress

920 Yonge St. Suite 1000
Toronto
, ON
M4W 3C7

Contact Info

Robert Cooper
(416) 964-1800
[email protected]
alterra.com

OHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory
OHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory
Alterra Group of CompaniesOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory

EXAMPLE 2

Altra Homes Inc.OHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory
OHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory
Altra Homes Inc.Address

345 Lakeshore Rd. E. Unit 402
Oakville
, ON
L6J 1J5

Contact Info

Tim Le Bas de Plumetot
(647) 533-8016

altrahomes.com

OHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory
OHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory
Altra Homes Inc.OHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory

EXAMPLE 3

Amacon Construction LimitedOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory
OHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory
Amacon Construction LimitedAddress

37 Bay St. Suite 400
Toronto
, ON
M5J 3B2

Contact Info

Frank Da Silva
(416) 369-9069
[email protected]
amacon.com

OHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory
OHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberOHBAOHBAOHBAOHBAOHBAMemberDirectory

Re: extract customer name and address from huge data dump in every cell

Thank you for replying AliGW!

I have tried that and, unfortunately, it left me with dozens of copies of the company name as well as 4 instances of "Contact Info", 2 of which actually have the customer's name trailing. And without having a preceding "MemberDirectory" followed by an "OHBA" as goal posts on either side of the last "Contact Info", I don't know how else to target what I'm after. Thanks anyway!

Happy Friday Oz!

I have a dataset where the data in nearly every cell in column A reaches excel's cap of 32,762 characters. Each cell contains a single customer's info, along with other useless data, repeated over and over in a pattern I can't quite decipher.

What I have noticed is, if I select the last occurrence of "Customer Info" as my mid point and then back up to the next left most occurrence of "MemberDirectory" as my left boundary, and then go forward to the first occurrence of "OHBA" as the right boundary (leaving "Customer Info" in the middle as my MID point), the customer information I need appears between these two boundaries. If anyone could help me write a formula to do this properly I would much appreciate it! I have attached a worksheet with some sample data. Thanks Oz!

I need to calculate the interval between phone calls for agents working in a call centre.

Example: An agent places a phone call at 10:00 AM with a duration of 3 minutes. They make their next call at 10:10 AM. The time between the end of the first call and the beginning of the second call equals an interval of 7 minutes. This is a straightforward calculation when dealing with call records for just one agent but more difficult (for me) when the data contains call records for multiple agents combined. My attempts using VLOOkUP and INDEX(MATCH have been unsuccessful thus far. Any assistance is much appreciated!

I have attached a sample sheet of some data, which is also pictured below. I have highlighted the formula which subtracts the start time of the previous call from the current call, less the duration of the previous call, to correctly arrive at the interval between calls, however, the formula does not account for the name of the agent so I have to adjust it manually for each call record which is what I'm trying to avoid.

[ATTACH=CONFIG]70762[/ATTACH]

forum.ozgrid.com/index.php?attachment/70755/

Re: Sum elapsed time between events, excluding weekends, for each customer in column

That's fantastic. It works beautifully. Thank you so much!

I've seen many related posts but none that address my problem exactly. I have a column of customers who applied for a loan. During the approval process each loan goes through a number of stages denoted by a change in the status. I have columns that track the time between stages. I'd like to summarize the total time between the start and end of the application process excluding Saturday's and Sunday's (ideally just Sunday's if the last event is on a Saturday). I have a formula that can do it using the NETWORKDAYS function but it requires knowing the row of the first and last date. So what I (think I) need is souped up version of this formula that recognizes the change in the customer's name in column C and incorporates those rows as the start/stop points. I've been playing with it for the last couple days but I haven't been able to figure it out. Any assistance greatly appreciated!

The formulas in the example is: =NETWORKDAYS(K40,[@[Date/time Status Changed]])-1-MOD(K40,1)+MOD([@[Date/time Status Changed]],1) where K40 is the first row of that customer and [@[Date/time Status Changed]]is the last row. Without the table notation the formula for that customer would be: =NETWORKDAYS(K50,K54)-1-MOD(K50,1)+MOD(K54,1).

I've also attached a worksheet with the formulas I have so far.

Re: vba to click webpage button with onmouseover and inside a frame

If possible, I suggest you use helper software like iMacros (imacros.net) I've experienced the same issue you have many times with hard to grab tags or tags that keep changing. Imacros allow you to open page in your browser, click on buttons, fill out forms, copy tables etc and it grabs/translates all the HTML tags for you. It's like using excel's macro recorder feature but in a browser. Once you have your imacros recorded, you can call them with a simple VBA script. They have literally saved me 100's of hours of coding. good luck

Re: Insert space 10 chars from end of row within indiv cells containing many rows

Thank you Jindon!! It worked perfectly. The actual data I needed to process was in column 37 so I changed:

a(i, 1) = .Replace(a(i, 1), " $1")

to

a(i, 37) = .Replace(a(i, 37), " $1")

Please let me know if there is anything else I should modify. Thank you very much.

I have a spreadsheet with about 10,000 rows where each cell is populated with data similar to the following:

0GoodwinWayne91 National CresBramptonONL7A 1G9
SLNmoved: GoodwinWPoplar DrBewdleyONK0L 1E09057972520
SLNmoved: GoodwinWilliam HFrench RiverDokisON 7057632387
Ph# add : BarnesR91 National CresBramptonONL7A 1G99058748199
Neigh : HalsteadN88 National CresBramptonONL7A 1G99059709708
Neigh : IrelandAndy93 National CresBramptonONL7A 1G99058402497
Neigh : ParasilitiG89 National CresBramptonONL7A 1G99058462622
Neigh : ShakyaT88 National CresBramptonONL7A 1G99059153080
SLN : GoodwinB75 Charolais BlvdBramptonONL6Y 2R89054500912
SLN : GoodwinChristopher28 Lsburgh DrBramptonONL6X 4Z49054529993
SLN : GoodwinG103 Manitou CresBramptonONL6S 2Z69052301779

forum.ozgrid.com/index.php?attachment/66333/

The challenge is to insert a space 10 characters from the end of each row - starting with the 2nd row -within the cell so that the phone# is separated from the postal code.
There is also a carriage return/line feed character at the end of each row. I have attached some actual sample data as well. Many thanks!!

This is what the data should look like
0GoodwinWayne91 National CresBramptonONL7A 1G9
SLNmoved: GoodwinWPoplar DrBewdleyONK0L 1E0 9057972520
SLNmoved: GoodwinWilliam HFrench RiverDokisON 7057632387
Ph# add : BarnesR91 National CresBramptonONL7A 1G9 9058748199
Neigh : HalsteadN88 National CresBramptonONL7A 1G9 9059709708
Neigh : IrelandAndy93 National CresBramptonONL7A 1G9 9058402497
Neigh : ParasilitiG89 National CresBramptonONL7A 1G9 9058462622
Neigh : ShakyaT88 National CresBramptonONL7A 1G9 9059153080
SLN : GoodwinB75 Charolais BlvdBramptonONL6Y 2R8 9054500912
SLN : GoodwinChristopher28 Lsburgh DrBramptonONL6X 4Z4 9054529993
SLN : GoodwinG103 Manitou CresBramptonONL6S 2Z6 9052301779

Re: Remove duplicate phone numbers on same row

Working perfectly!! Thank you so much!! (please note I had to add "Dim a, x, i,ii As Long, txt As String" at the beginning)

Re: Remove duplicate phone numbers on same row

Hi Jindon, I've been using your code and noticed that it is truncating some of the phone numbers. It also seems to be editing columns that do not contain phone numbers. I've attached some sample data if you would be so kind to take a look.forum.ozgrid.com/index.php?attachment/65156/

Re: Remove duplicate phone numbers on same row

When I run it on the raw csv files it runs fine. I have another macro that converts the csv files to tables upon opening but when I turn that off it elimiates the type mismatch error. The only issue remaining is that it seems to be corrupting some of the phone numbers. Are you seeing that with the test data provided? When I run it on the test data the phone number in H4 gets shortened.