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.

Is it possible to subtract data columns in excel?

Discussion in 'Business Applications' started by wmitz, Apr 23, 2007.

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

    wmitz Thread Starter

    Joined:
    Apr 23, 2007
    Messages:
    10
    I'm trying to find a way to delete some data from a column, say column A. The numbers to be deleted are in another column, column B. Is there a way to have excel remove from column A, those numbers that appear in column B? It would require some way to compare, but I can't figure out if excel has this capability. If the columns were small, we could do this manually, but the data set is huge, with several thousand numbers.

    Thanks for any help.
    -wayne
     
  2. MRdNk

    MRdNk

    Joined:
    Apr 7, 2007
    Messages:
    439
    Hey wmitz,

    Try inserting a third column, column C, and using this formulae
    =IF(A2=B2,"",A2)
    Assuming that you are start on row 2 with your data, then drag that for the whole column. Once thats done, check that C2 is now as you want it, if you're happy then copy the whole column and paste is to column A, use paste special and select paste values.

    I strongly advise that you have a copy of the whole spreadsheet just in case it doesn't do exactly what you what it to do.
     
  3. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Alas, that will only work if the equivalent values are next to each other - if column A has 1,2,3...50 and column B has 2,4,7,11...44, (say 20 values as compared to 50) then unless you make B 0,2,0,4,0,0,7.... you will not get matches.
    If your data is numerical, you can use VLOOKUP. This would be very easy in Access, also.
    Actually, just got to looking - go to http://www.asap-utilities.com/index.php and get their utility for Excel. Once you have it installed, go to Range, empty duplicates in selection. You will have to have the short list on the right (that is, in column A, and the complete list in column B) - the program will compare what is in column A and remove the duplicate numbers from column B.
     
  4. deej

    deej

    Joined:
    Jun 11, 2003
    Messages:
    152
    This may go some way toward solving your problem. Try MRdNK's suggestion but instead use the following formula in row 1 of column C: =IF(COUNTIF(B:B,A1)>0,"",A1). Replicate this as far down the third column as far as you need to go (i.e. down to the last row in column A), you should find that numbers from column A are displayed in column C only if they dont appear in column B. The limitation with either of these suggestions is that you have to take your final list (after deletions) from column C, not column A. You can't get it actually delete the numbers from column A, I think you'd need to write a macro for that. I could probably put a macro together to do the job if you want to go that far (but not for a few days as too busy I'm afraid)

    Deej.
     
  5. wmitz

    wmitz Thread Starter

    Joined:
    Apr 23, 2007
    Messages:
    10
    Thanks Deej, that is a really clever solution. But unfortunately for our needs, it still has one problem. We need to allow the numbers in column B to be used only once. As it stands now, none of the numbers in A that match a single number in B will be moved to column C. Unfortunately, we do have several of the same numbers repeated in column A (they arise from measurements of linear dimensions in histologic images). So, we need a way to somehow let the numbers in column B be used only once. Column A will contain all of the numbers in column B, but just a whole lot more. We need just this difference between the two data sets.
    I realize you are being quite nice in offering this help, and if you can come up with any new ideas that would be wonderful. We are not in a great rush, so please work at your most comfortable pace. Thanks again.
    -wayne
     
  6. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Did you check out the utility I suggested in post #3? It will do everything you want.
     
  7. wmitz

    wmitz Thread Starter

    Joined:
    Apr 23, 2007
    Messages:
    10
    No, I didn't immediately look at it, since it isn't Mac compatible. But now looking at the website and software, it sure does look like it would be perfect. And since we do have a PC where it will run, it just involves sending the excel files from the Mac (which has the image analysis camera) over to the PC. Not a problem at all.
    Thanks for the tip. It really is a nice utility
    -wayne
     
  8. deej

    deej

    Joined:
    Jun 11, 2003
    Messages:
    152
    Hi Wayne

    I'll do some thinking anyway, just in case you run into problems porting your data across from Mac to PC. Can you clarify: if a number appears more than once (i.e. 'n' times) in column A and once in column B do you want the resulting column (C in our example) to hold 'n - 1' occurrences of the number? So: if 17 appears 3 times in A and 1 time in B then C will hold 2 occurrences of 17. If so, how do you decide which occurrence to delete from A?

    All the best
    Deej
     
  9. wmitz

    wmitz Thread Starter

    Joined:
    Apr 23, 2007
    Messages:
    10
    Thanks again, deej. It surely would be most convenient if we could do it directly on the Mac.

    Your assumption is correct. C should hold in your example n-1 occurrences, or more generally, if a number appears k times in B and n times in A, it should appear n-k times in C. n-k will always be > or = to 0. So once a number from B is used to 'delete' a number from A, it itself shouldn't be used any more. It doesn't matter which occurences in A are deleted. They are just numbers in a distribution for our analysis.

    Best regards
    -wayne
     
  10. wmitz

    wmitz Thread Starter

    Joined:
    Apr 23, 2007
    Messages:
    10
    Well Slurpee, i now have looked more closely at the ASAP-utility. While it really is a terrific add-on to excel, it unfortunately, has no way to combine data sets as I need. It can combine data, but I was unable to find any way to remove common numbers from different data sets. So maybe Deej will come up with something that can do this. But thanks anyway for the tip on ASAP, which will have other uses.
    -wayne
     
  11. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    If you move your dupes to column A and your main data in Column B, highlight both columns, then you can use Range, Empty Duplicates in Selection. ASAP will look first in column A (hence leaving them) and take out all other cases after where a number in A first occurs (even if it is in A again). You could do this with the main data in A, but if, say, a number you wanted deduped was first found in column A, it would take it out of the list in B.
    What this will not do, however, is leave multiple cases of the number if you want there to be more than one - it is best to fool around with this feature until you know what the limits are.
    See the attached for a simple before and after shot. (Oh, the really, really nice thing is that this takes seconds, not hours of messing around).
     

    Attached Files:

  12. wmitz

    wmitz Thread Starter

    Joined:
    Apr 23, 2007
    Messages:
    10
    yes, you are right that it only takes seconds to play around. And indeed ASAP is fun to play with. But while this Range function does do what you say quite well, it still won't serve what we need, since we can't get rid of all but one of the duplicates. We still need to get rid of the just the duplicates in both, but leave all extras with that same value in the longer column.
    But we're still trying. It is a very interesting side projects ;-)
    -wayne
     
  13. deej

    deej

    Joined:
    Jun 11, 2003
    Messages:
    152
    Hi Wayne

    Pity you couldn't get exactly what you need from Slurpee's utility - sounded quite hopeful there for a while. I have got a little further with my thinking on this one and have got closer to what I think you want - but not quite there yet! I have another couple of questions for you:

    (i) is the sequence of numbers in the resulting column significant? That is - does it matter if the process of eliminating duplicates changes the order in which the numbers are presented in the final list? If you're using the values for statistical analysis of distribution I guess the order of occurrence might be significant (I'm no statistics expert but I know the stattos I work with are sometimes quite particular about that sort of thing!)

    (ii) is there any possibilty of importing your data into an Access database? I know it's technically a simple process, but is there any reason (for your use of the data) why this shouldn't be done? I'm not suggesting that you should do this, but it would give us a few more tools to play with ...

    That's all for now; I have a few chores to be getting on with - but I'll still be thinking!

    Deej
     
  14. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Yes, actually, I work doing stats myself, so let us know a bit more about what you are using this for - where do you get the two collections of data (samples? test results?) and why is it important to remove the just one (the first would be easiest) number that matches on in the other column. Thinking about it, might be possible to select the first number in column B (the short list) find the first match in column A and delete bot, then return until B is empty....
     
  15. wmitz

    wmitz Thread Starter

    Joined:
    Apr 23, 2007
    Messages:
    10
    Ok guys, here's more detail. (i) The sequence doesn't matter. We can reorder the numbers and plot a cumulative distribution (ii) Someone else suggested that Access might e able to do this. We could get it into Access if needed. The version of Office on our Mac doesn't hve Access, and I have no experience with it, but the PC Office version does, so that might be worth a try if you can sucggest what to do.

    The data come analysis of histologic measurements using ImageJ. We are measuring lengths of structures in an area. We need to compare measurements close to the border to those more interior, and to do this we can measure the total area, then digitally "peel away" the tissue in the desired region close to the border. Then we measure in the area that's left. So now we need to just subrtract the two data sets to get the measurements in the peeled away region. FYI , this is related to a research project where I work at the Johns Hopkins School of Public Health.

    I hope this clarifies things. Let me know if you have any further questions.
    -wayne
     
  16. Sponsor

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 - possible subtract data
  1. kramerica
    Replies:
    3
    Views:
    237
Thread Status:
Not open for further replies.

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

  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