Sign in to follow this  
Followers 0
workinglikeadog

Microsoft Excel help

74 posts in this topic

Love to, but I have units, factories, levels and all sorts of other issues. And there are 370,000 entries. :shutup:

Share this post


Link to post
Share on other sites

I think I might go down the text to columns path though, then sort on vacant columns to the right. That should leave me with only 3 or 4 different groups. 1 will be right, then the rest I just concatenate the first 2 or 3 columns.

It might just work.

Share this post


Link to post
Share on other sites

Why not put in a holder field that counts the number of commas in the original field. Not sure of the formula but I bet it exists.

 

Then you can parse away with a conditional formula that finds the third from the last comma, returns everything to the left of that one, then the text between it and the next, etc. It might take some trial and error to get the IFs, MID, FIND, etc 100% bullet proof, but if you have that many records it will be worth the effort.

Share this post


Link to post
Share on other sites

I know nothing about Excel but there should be an easy way to replace the last comma found in that string with a different character, i.e. a semi-colon, and then use text to columns with the semi;colon as the separator. Repeat the process for the remaining commas.

Share this post


Link to post
Share on other sites

I know this might not be the answer you're looking for, but I wouldn't use Excel as I'd code something very quickly (it's about 5-10 lines of code) and work from the back of the string. The last field is STATE, the next is CITY, then treat all the next as STREET. Then I'd reformat using a TAB instead of a comma between STREET, CITY and STATE, and then open in Excel as a TSV (tab separated value) file.

 

How big is the file?

Share this post


Link to post
Share on other sites

My turn to beg for help this time.

 

I have a large list of addresses that I need to break into street, Suburb & State. The formatting is consistent, with a comma between the fields I need, the only issue is that many have a sub-address as well which can have 1 or 2 commas in it. The sub-address is to be included in the street address. I can work out how to split the text string from the left using the commas, but if I do that, then some addresses will get broken into 4 or 5 fields rather than 3. Is there a way to do it from the right, so I can peel out State, then Suburb, then the rest.

 

eg:

 

 

17 CHURCH ST, PERTH, WA

3 PLAIN ST, EAST PERTH, WA

U1, 23 QUEENSBERRY ST, CARLTON, VIC

U1, 41-53 VICTORIA PDE, FITZROY, VIC

 

What I want from the list above is:

 

17 CHURCH ST PERTH WA

3 PLAIN ST EAST PERTH WA

U1, 23 QUEENSBERRY ST CARLTON VIC

U1, 41-53 VICTORIA PDE FITZROY VIC

 

It's doable in Excel directly, provided that:

1. all addresses are in this format, i.e. all have state code at the end preceded by a comma and a space

2. all the city/suburb names are also preceded by a comma and a space

 

If you have an address as list in cell A1, then in cells listed put in these formula:

Cell B1:

=LEFT(LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1))))-2),LEN(LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1))))-2))-LEN(TRIM(RIGHT(SUBSTITUTE(LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1))))-2),",",REPT(" ",LEN(LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1))))-2)))),LEN(LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1))))-2)))))-2)

 

 

Cell C1:

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1))))-2),",",REPT(" ",LEN(LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1))))-2)))),LEN(LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1))))-2))))

 

 

Cell D1:

=TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1)))

 

Then simply copy down the formula in your sheet

 

Then you would copy and paste all those new column values into another sheet (so you have the strings and not the formula)

Edited by Alex Simmons
1 person likes this

Share this post


Link to post
Share on other sites

You're a god-send Alex. It works brilliantly, and simple. Hell of a formula, & I'd hate to be working out what it's doing, but it works.

 

Thanks

Share this post


Link to post
Share on other sites

I should have said the formula were for cells B1, C1 and D1 respectively (i.e. all in the same row), and not B1, B2 and B3 but I'm sure you got that. I'm going to edit it just in case!

 

The formula in cell D1 is the key - it scans the string from the right for first instance of the character inside the quote marks right after

SUBSTITUTE(A1,","

 

and then returns whatever the string to the right of that character is, and removes any leading spaces.

 

The formula in C1 does the same thing after first trimming off the length of string (+ 2 character for the space and comma) from the right found by the formula in cell D1.

 

and same for B1, but trimming using the length of string found in C1 + 2.

Share this post


Link to post
Share on other sites

Been a while, but need to work through the following problem. I have a spreadsheet where the data is entered in the following format (in one column). I cannot work out how to sort so they are in order of older to newest - it keeps putting all the '1's' together (so 1, 11, 12...2, 20... This is a client referral list.

 

Basically - I added the year to help...but not solving the full problem (it does seperate out 2015, 2016 referrals). This reflects year, term (1,2,3,4), then the referral number. I need it in correct order so new referrals can be, after various filters / report have been run.

 

2017 T1/001

2017 T1/002

2017 T1/003

.

.

.

2017 T2/001

.

 

 

Any advice, as I don't want to add seperate rows for Year, Term, Column.....

Edited by skel

Share this post


Link to post
Share on other sites

Not sure what the problem is.

With data in that format I just hit the ascending sort button and it was done.

Share this post


Link to post
Share on other sites

Skel, as Alex said, it should just sort itself easily, unless you have something like

 

2017 T1/001

2017 T1/002

2017 T1/003

......

2017 T1/00010

If the referral numbers don't have the same number of digits, it will throw things out, as it's actually sorting alphabetically, not numerically.

Share this post


Link to post
Share on other sites

Highlight the cells, right-click and select Format Cells...

 

What's it say in the "Numbers" tab?

 

I've tried replicating your issue, but as others have said, it sorts correctly (I have a hangover and am struggling to focus on the 1.5 hour presentation I have to write today and present tomorrow, spreadsheets are a pleasant distraction).

Share this post


Link to post
Share on other sites

Been a while, but need to work through the following problem. I have a spreadsheet where the data is entered in the following format (in one column). I cannot work out how to sort so they are in order of older to newest - it keeps putting all the '1's' together (so 1, 11, 12...2, 20... This is a client referral list.

 

Basically - I added the year to help...but not solving the full problem (it does seperate out 2015, 2016 referrals). This reflects year, term (1,2,3,4), then the referral number. I need it in correct order so new referrals can be, after various filters / report have been run.

 

2017 T1/001

2017 T1/002

2017 T1/003

.

.

.

2017 T2/001

.

 

 

Any advice, as I don't want to add seperate rows for Year, Term, Column.....

Have you tried separating the data by using "Text to Columns", fixed width, and separating it into three columns? You can then sort by year, Term and then number, which will give you consecutive numbers for the last column. This should prevent your data presenting as ... 1, 12, 13, etc. If you need to consolidate the data back into one column then use "&" between each cell reference to bring the three columns back into one.

eg. =+G6&H6&I6

 

..... however, it's highly likely that I'm completely wrong :)

Edited by Catcam
1 person likes this

Share this post


Link to post
Share on other sites

To reassemble text from different cells you can also use =CONCATENATE(text1,text2,etc). Insert spaces by using 2 double inverted commas instead of a cell name, or insert a piece of text in double inverted commas.

1 person likes this

Share this post


Link to post
Share on other sites

Concatenate is great if the range is contiguous, but otherwise, & has less tying.

Share this post


Link to post
Share on other sites

I need help with some trend graphs. The data covers a period of 2 years.

 

I have about 12000 lines of data. One of the columns is a date. Another is the "type" of entry. I want to produce trend graphs showing the numbers of each entry "type" per month for the 2 years. Whenever I try to graph it does it by day, not by month.

 

How do I get around this?

Share this post


Link to post
Share on other sites

I'm sure that those here with more experience can give a better answer, but I believe that you need to use a pivot chart and group by month...

Share this post


Link to post
Share on other sites

Use a Pivot table and group the data by month. Plot the output of the pivot table.

Share this post


Link to post
Share on other sites

Thanks guys. I used the same approach, though didn't realise you could group by month, so I actually created an extra column with the month, and used that as my reference rather than the actual date. I'll use your method for future requirements.

Share this post


Link to post
Share on other sites

Along similar lines to the last question.

I have data entries, and each line represents a job. There is a "STARTDATE" and an "ENDDATE". How do I plot line graphs showing work coming in & out? I can't get any sort of pivot table/graph to show what I want.

Example below.

Data:

FEASIBILITY                   STARTDATE         ENDDATE

WF-181078 01/07/2016 11/07/2016
WF-181079 01/07/2016 11/07/2016
WF-181080 01/07/2016 19/07/2016
WF-181082 01/07/2016 06/07/2016
WF-181086 01/07/2016 21/07/2016
WF-181087 01/07/2016 04/07/2016
WF-181088 01/07/2016 04/07/2016
WF-181106 01/07/2016 01/07/2016
WF-181126 01/07/2016 07/07/2016
WF-181127 01/07/2016 04/07/2016
WF-181129 01/07/2016 04/07/2016
WF-181132 01/07/2016 06/07/2016
WF-181136 07/07/2016 11/07/2016
WF-181138 01/07/2016 06/07/2016
WF-181151 01/07/2016 21/07/2016
WF-181152 01/07/2016 04/07/2016
WF-181154 01/07/2016 21/07/2016
WF-181158 01/07/2016 20/07/2016
WF-181162 01/07/2016 08/07/2016
WF-181164 01/07/2016 20/07/2016
WF-181189 01/07/2016 11/07/2016
WF-181197 01/07/2016 21/07/2016
WF-181205 01/07/2016 06/07/2016
WF-181217 01/07/2016 11/07/2016
WF-181218 01/07/2016 07/07/2016
WF-181226 01/07/2016 05/07/2016
WF-181231 01/07/2016 11/07/2016
WF-181233 01/07/2016 11/07/2016
WF-181235 01/07/2016 19/07/2016
WF-181243 01/07/2016 06/07/2016
WF-181249 01/07/2016 21/07/2016
WF-181251 01/07/2016 04/07/2016
WF-181252 01/07/2016 21/07/2016
WF-181253 01/07/2016 13/07/2016
WF-181257 01/07/2016 11/07/2016
WF-181258 01/07/2016 04/07/2016
WF-181259 01/07/2016 13/07/2016
WF-181261 01/07/2016 14/07/2016
WF-181262 01/07/2016 12/07/2016
WF-181269 01/07/2016 04/07/2016
WF-181287 01/07/2016 04/07/2016
WF-181296 01/07/2016 11/07/2016
WF-181301 01/07/2016 04/07/2016
WF-181303 01/07/2016 05/07/2016
WF-181306 01/07/2016 04/07/2016
WF-181313 01/07/2016 05/07/2016
WF-181320 01/07/2016 04/07/2016
WF-181321 01/07/2016 20/07/2016
WF-181324 01/07/2016 04/07/2016
WF-181329 04/07/2016 18/07/2016
WF-181330 01/07/2016 11/07/2016
WF-181332 01/07/2016 11/07/2016
WF-181333 04/07/2016 11/07/2016
WF-181334 04/07/2016 11/07/2016
WF-181335 04/07/2016 11/07/2016
WF-181336 04/07/2016 11/07/2016
WF-181337 04/07/2016 11/07/2016
WF-181338 04/07/2016 11/07/2016
WF-181339 04/07/2016 11/07/2016
WF-181340 04/07/2016 11/07/2016
WF-181342 04/07/2016 15/07/2016
WF-181345 01/07/2016 06/07/2016
WF-181347 01/07/2016 04/07/2016
WF-181349 01/07/2016 13/07/2016
WF-181350 01/07/2016 19/07/2016
WF-181354 01/07/2016 19/07/2016
WF-181378 04/07/2016 21/07/2016
WF-181379 04/07/2016 08/07/2016
WF-181380 04/07/2016 11/07/2016
WF-181387 04/07/2016 20/07/2016
WF-181393 04/07/2016 21/07/2016
WF-181395 04/07/2016 21/07/2016
WF-181404 04/07/2016 20/07/2016
WF-181405 04/07/2016 21/07/2016
WF-181406 04/07/2016 20/07/2016
     

 

I want the graph to look like this:

 

Graph.JPG.5e064d8c71b2e77566e77a34b46e08f8.JPG

Edited by Ex-Hasbeen

Share this post


Link to post
Share on other sites

Don't understand what you are trying to plot. Are you trying to visualise a work flow of some kind?

Share this post


Link to post
Share on other sites

Yes. One line shows how many entries (Feasibilities) have a start date each day, and the other shows how many entries have an end date each day. This is just a small sample of a much larger table.

Share this post


Link to post
Share on other sites

Create a table with a frequency array formula.

Then can plot the lines in the manner you are after.

Sample shown using your sample data. You need to create a table with the day for each date in the date range for the dataset and then create an array formula as shown:

2017-04-21_110816_zpsg0mpfibw.jpg

2017-04-21_111216_zpswwbo81e1.jpg

2017-04-21_110842_zpsbxevx4w4.jpg

 

For an array forumla, you need to select the range you want to enter it, type in the formula and rather than hit <Enter>, you hit <shift>-<Ctrl>-<Enter>

Share this post


Link to post
Share on other sites

Actually to make the array formula easier, can be just:

In column F:

=FREQUENCY(B:B,$E:$E)

In column G:

=FREQUENCY(C:C,$E:$E)

where Column E contains the dates for the date range you are interested in plotting, with 1 day per cell row. If you enter the first date in top cell and then simply copy down a formula which adds 1 to the cell above, it will auto update based on the start date

Edited by Alex Simmons

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0