Jump to content
workinglikeadog

Microsoft Excel help

Recommended Posts

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
  • Like 1

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

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
  • Like 1

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.

  • Like 1

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

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

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

Back into work for 2018.  Excel question – I have set up a spreadsheet for an early development service and I need to make another small change for the new school year.

In the main worksheet there are multiple columns – including proposed school (the list of schools is a drop down option with this list of school names in a hidden another worksheet – so admin just scroll through the drop down list and select).  We want to keep this the same.

The change I need to make is to add in another column ‘catchment school’ – however we would like this to automatically fill in with the school name that aligns to suburb name (main worksheet) where the family lives.  So when admin fill in suburb (column I) catchment school (column AD) auto populates from the list on a second worksheet (column F rows 2 to 140)

 

So, if  child lives in Redlynch, how can I set a formula to scan the existing list of 130 schools and auto populate with Redlynch College

 

Thanks in advance

Share this post


Link to post
Share on other sites

Okay - have been googling and playing around (as really do want to learn how to enter formulas) and come up with the following - but it is saying it is either too short or something with symbols.  I am wondering if this is due to the suburb not matching the school exactly - suburb would be just Redlynch or Freshwater - but school will actually be Redlynch College or Freshwater Primary School.......

=VLOOKUP(I2,Sheet3!F2:F140,30,[FALSE])

I2 - suburb

sheet 3 F2:F140 - is the lookup list

30 - column number where to enter outcomes

FALSE as EXACT

 

 

Share this post


Link to post
Share on other sites

Skel,

Do you have a list of the suburbs that feed to particular schools? That's what vlookup will be looking for.

eg:

image.png.5062e0e7cbdfae3b49e024b52287f4bb.png

  • Like 1

Share this post


Link to post
Share on other sites

Yes - but not that specific (just suburbs and a list of schools) - not sure how to set up a list that would be that specific to be incorporated into a formula

That type of list be much better as various suburbs do feed into one school!

Edited by skel

Share this post


Link to post
Share on other sites

Is there not a designated catchment area for each school. Dept of School Education should be able to supply it. 

If it’s not a neat division, that is if one suburb feeds several schools, then you need more address information than just a suburb name. Indeed, it goes beyond a simple spreadsheet problem and moves into the world of GIS and mapping. 

Share this post


Link to post
Share on other sites

That what I am now thinking.  So if was to go back to my original idea, just aligning a generally suburb to school - will it work if I have Redlynch College and the suburb is just Redlynch.   Thanks for your patience _ am googling and You Tubing at the same time too.....

This is what I tried initially,  but is comes up with an error

=VLOOKUP(I2,Sheet3!F2:F140,30,[FALSE])

Being:

i2 - suburb manually entered

sheet 3 F2:F140 - is the lookup list

30 - column number where to enter outcomes

FALSE as want an 'exact match'

Share this post


Link to post
Share on other sites

Try this

=VLOOKUP(I2,Sheet3!F2:AI140,30,[FALSE])

 

For that formula to work the table on Sheet 3 would need to be sorted by suburb and the suburbs would need to be listed in column F and the relevant school listed in Column AI (i.e 30 Columns to the left of the suburb)

Share this post


Link to post
Share on other sites

No  - think I am missing something simple and just can't work it out.  Amazingly,  there is no one in our regional office who can help either....:mellow:

I did change one thing in that it would 21 columns from column F where the data goes.....(column AD)

Enjoying my first day back....lol.  This is how it is set up at the moment, so if Mossman was typed in as the suburb, Mossman SS would be the catchment school.  Are my list set up wrong?

Capture.PNG

2.PNG

Edited by skel

Share this post


Link to post
Share on other sites

The table with schools and suburbs has to be sorted alphabetically by suburb for vlookup to work 

Share this post


Link to post
Share on other sites
3 hours ago, Limited said:

The table with schools and suburbs has to be sorted alphabetically by suburb for vlookup to work 

Not so. Use the False flag within vlookup and it only returns a precise match. Regardless of table order. I can recollect the one application (in 20+ years of being a serious excel user)  where I needed to omit the false. 

Share this post


Link to post
Share on other sites
4 hours ago, skel said:

No  - think I am missing something simple and just can't work it out.  Amazingly,  there is no one in our regional office who can help either....:mellow:

I did change one thing in that it would 21 columns from column F where the data goes.....(column AD)

Enjoying my first day back....lol.  This is how it is set up at the moment, so if Mossman was typed in as the suburb, Mossman SS would be the catchment school.  Are my list set up wrong?

Capture.PNG

2.PNG

Skel I think you are over thinking this. I assume that any suburb can only feed only one school, but one school might be fed by one or more suburbs. Correct?  This is called a one to-many relationship, perfect for excel lookups. 

Then you need a very simple lookup reference table. I’d put it on its own tab within your spreadsheet. The lookup needs just two columns

A a list of every suburb, one line each (ie, they are unique)

B the school that is fed by that suburb.  The same school could appear in more than one line. 

If one suburb feeds more than one school this won’t work. This is a many-to-many relationship that won’t work using lookup. You would need to split the suburb up somehow (usually by street address) which is not a simple spreadsheet application. 

 

 

  • Like 1

Share this post


Link to post
Share on other sites
8 hours ago, skel said:

30 - column number where to enter outcomes

Your Vlookup would try to RETURN a value from column 30 in your lookup table, but your lookup table only has one column.

 

Edited by Tyno

Share this post


Link to post
Share on other sites

Thanks everyone!!! So I think I  understand that I need to have suburb and school in a separate worksheet - this then links to "suburb" when entered in on the main spreadsheet.....  For the purpose for this spreadsheet, simple suburb / school will work as feeder concept will be too complex (as many suburbs could go to one school).

So.....this still didn't compute (but think I am getting closer)   =VLOOKUP(I2,Sheet2!A1:B10,2,[FALSE])

(sheet 1) Column I = Suburb (as entered by the admin)  on the main tracking spreadsheet

(sheet 1) Column AD - where I want the school name to be populate

(sheet 2) Suburb / school - column A/B (as per picture below)

Do I have the first value wrong?

Ref table.PNG

Edited by skel

Share this post


Link to post
Share on other sites

I DID IT!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

:o:o:o:o:o:o:o:o:o:o:o:o

 

Thanks everyone!  :-)  Feeling pretty chuffed with myself......I just needed to remove the titles suburb / school

  • Like 2
  • Thanks 1

Share this post


Link to post
Share on other sites

One more slight problem - the document size has now grown to 40MB.  I have googled and worked through the options suggested, like clearing all the remaining cells in each worksheet (which reduced it from 128MB).  There are only 5 worksheets, 3 with data (about 600 lines each, 30 columns) and the other two are data list only (very minimal).  Only one of the worksheets has working formulas and is only set up for 500 rows. Would the VLOOKUP formula have added that much?  There are two others formula in play - one for calculating age and start date for Prep. 

Share this post


Link to post
Share on other sites

Do you have any links to other files, or data generated by formulas, that you can now copy & paste back in as straight data?

Share this post


Link to post
Share on other sites

Checked all the others aspects above. Nothing as listed.

Have redone the clear all other cells and it has gone to 20MB - so getting a big better. :D

Edited by skel

Share this post


Link to post
Share on other sites
16 hours ago, Tyno said:

ctrl-c -> ctrl-v into new sheet.

 

Sometimes the obvious is the answer - thank you everyone for your help.  I now really do understand how to do VLOOKUP and ensuring documents are the right size. :D  And I know the service is also super delighted with update and snazzy version! It will make it so much easier to track incoming referrals and actually run report on the volume of request!

Share this post


Link to post
Share on other sites

Hello......my six monthly check in.....

I am trying to gather together data for two sunsets of schools, 8 in one group and 9 in the other.  The senior leadership want baseline data for each group. 

Some data is simply just adding and divding, as I can access raw number. Some data sets are in percentages for that school.   Simple if it was just a single school. But one school may have 100 students, another 50.  Is there a formula to work backwards to go from a percentage to a number.  Once I have the raw numbers I can work out totals and graphs.

So, 78% of 215, then 82% of 67 etc etc.

 

 

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

×