1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel 2010 separating data from one column to multiple columns

Discussion in 'All Other Software' started by Phate21, Feb 25, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. Phate21

    Phate21 Thread Starter

    Joined:
    Feb 25, 2013
    Messages:
    2
    Hi,

    I’m trying to work out how I can look at one column in a spreadsheet and display the text in another field but without a load of blank cells in the results column. Let me give you an example.

    On Sheet1
    In column A are road names
    In column B the number of building on that road
    In column C the last date and work was doneon the road
    In Column D is an =IF formula thatbasically gives a result of 1, 2, 3 or 4.
    On another sheet I want four lists

    Sheet 2
    In column A is a list of road names thathave a value of 1 in Sheet1 column D
    In column B is a list of road names thathave a value of 2 in Sheet1 column D
    In column C is a list of road names thathave a value of 3 in Sheet1 column D
    In column D is a list of road names thathave a value of 4 in Sheet1 column D

    I can do this with a simple IF statement in columns A,B,C & D on sheet2 and drag it down but there are a couple of problems with this method.
    1, If the value in sheet1 doesn’t match I get a blank cell on sheet2 (as there should be) but this leaves a large number of cells with no value. I need a list in each column A,B,C & D onsheet2 without any blank cells.
    2, I’m going to be pasting different data into columns A,B & D on sheet 1 a number of times and the number of rows in will be different each time.

    I remember seeing something before thatinvolved using the IF and COUNTIF function in a formula but not sure how.
    Any help would be appreciated
     
  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,891
    First Name:
    Wayne
    should be able to use an array formula - so use control + shift + enter key and the formula gets { } brackets
    Code:
    =INDEX(Sheet1!$A$2:$A$14,SMALL(IF(Sheet1!$D$2:$D$14[COLOR="Red"]=1[/COLOR],ROW(Sheet1!$A$2:$A$14)-ROW(Sheet1!$A$2)+1),ROWS(F$4:F4)))
    where you change the red =1
    to =2, 3, 4 for different columns

    does that work out

    see attached

    if it works we can extend the range and cleanup the errors
     

    Attached Files:

  3. Phate21

    Phate21 Thread Starter

    Joined:
    Feb 25, 2013
    Messages:
    2
    Hi etaf

    Thanks for getting back to me.

    Yes this is what I need to do. Couple of issues though. are you aware that the first cell in your example on Sheet2 F3 doesn't update properly when the data on Sheet one is changed. Also if I inset new roads etc. i.e. road23 - road30 on sheet one the new roads aren't included in sheet2

    Thanks
     
  4. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,891
    First Name:
    Wayne
    for some reason the first cell
    sheet2 F3 - does not have an array formula in

    if you click on the cell
    then click in the formula bar and
    control + shift +enter - use all three keys together - you should see { } appear around the formula
    and will work now
    OR
    copy cell F4 upto F3

    the range in the formulas only cover upto row14

    so that just needs extending - I have extended to 1000 rows and also added error correction

    see attached

    I thought you would use the formulas in another spreadsheet and this was only an example - hence the change -

    let me know if that works ok now
     

    Attached Files:

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Excel 2010 separating
  1. snoozee
    Replies:
    0
    Views:
    113
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1090901

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice