Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Find match between columns and delete non-matching cells

I see that you've helped numerous people; Could you please look at my simple problem.....(It's hard for me; but a piece of cake for you I'm sure.) Thanks!

I have this problem!

I have a spreadsheet with products I need to match another column with pictures. I have about 200 products and only 140 pictures. How can I match them up and remove the ones that don't have a cooresponding picture? I've enclosed the Excel file. Thanks!

Column A - Products Column F- Picture File = If These match

If they don't match - delete them so I have one clean file with products and picture files by row. I hope that helps! Thanks!example.xls


Post your answer or comment

comments powered by Disqus
Hello,

I would like a Macro that Deletes all Rows in Column A (Sheet 1) that don't have a match in Column A (Sheet 2).

For example if I have:
Sheet 1

A B

1 50
4 20
6 53
2 42
7 48

and Sheet 2

A B

1 156
6 15
2 14

After using the Macro to have in Sheet 1 only:

A B

1 50
6 53
2 42

Thank You in Advance

This probably should be broken down into two posts but:

First, what is the best way of going through a column and deleting anything that is not equal to x or y?

Next, what would be the best way of going through a column and replacing certain ids (e.g. xy548934, gd348343) with names (e.g. Bob, Joe)?

I have a large excel file where I am willing to copy the third CELL to the next column and deleting the second column below the cell containing "PM0A"

for eg.
situation right now ------------->
1st cell --> PM0A
2nd cell--> 35648 (variable)
3rd cell --> 3 (variable)

situation required ---------------->
1st row --> PM0A 3 (in next column)
2nd row --> (blank)
3rd row --> (blank)

thanks..

I'm using MS Excel 2003

I have a total data sheet on 'sheet 1' and an imported data sheet on 'sheet 2'. In both sheets Column A has a case ID number.

What I would like is a Macro to compare the two columns (sheet 1 Column A & Sheet 2 Column A) and any NON matches from sheet 2 copy the entire row A:J and paste on Sheet 3.

I should also add that all data starts in row 2 as row 1 contains headers.

I hope the above makes sense and there is a way to action my request.

thanks

Hello all. I have looked through the forum but have not yet come across a solution to my specific problem. I need some code that will look at cells in several columns (A, B, C, and G in the attached file), and delete the row on the OrigSheet if all 4 cells match the same columns on the ReferenceSheet. The OrigSheetAfterCode shows the desired results. I have over 186,000 rows to sort through and I would like to be able to modify the columns to compare against for future comparisons.

Thanks,
Andrew

I have two lists of email addresses in columns and I need to find the matches between columns and have those addresses returned to perhaps a third column. Any ideas? Thanks!

I am intermediate excel user (at best) and I need assistance creating a formula and I have no idea how to go about this.

I have a sheet open with inventory from a distributor. In column A I have SKU's and in column F I have SKU's. When Column A matches the SKU in Column F i need for the price listed in column B to list in new Column J.

Note: The SKU's are not in order and several SKUs can be skipped in between. If there are no matching SKU's in Column B then it can just be skipped.

Thank you in advance for the help!!!! I have been stuck for days!

I need a macro that deletes rows in tab "A" if the IDs in column A do not
match those in column "A" of the master list on tab "B". So, if the IDs
match, then they're kept; non matches are deleted.
I tried to modify a delete duplicates macro I use regularly, but have had no
luck.
Thanks for your help.
Excel User

Hello,

I found a function that founds the last used cell in a column.


	VB:
	
 
    Dim WS As Worksheet 
    Dim LastCell As Range 
    Dim LastCellRowNumber As Long 
     
    Set WS = Worksheets("Sheet1") 
    With WS 
        Set LastCell = .Cells(.Rows.Count, "A").End(xlUp) 
        LastCellRowNumber = LastCell.Row 
    End With 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Now I need to delete all the rows above that row with a blanc cell in that column.

So the macro must find the last cell in a column, and then delete all the rows above that have a blanc cell in this column.

Can somebody help?

Grtz

aisietie

HOw can I match combination columns of A-F (row1) to combination H-M (rows1 to end) and show how
many are number matched in column N..

ex..
Rows

A

B

C

D

E

F

G

H

I

J

K

L

M

N

1

2

3

16

17

11

9

11

17

18

33

45

1

2

2

5

10

12

26

42

16

42

6

5

16

18

11

3

3

18

17

12

9

11

5

28

26

31

17

18

7

2

4…

…

…

..

1000

I have a file here included which has 8 + columns. I normally have to go through each column and find when the references start and delete everything below it. If i could have a macro find where the references start in each column and delete on down that would be a huge time saver.

#REF!
#REF!
#REF!
#REF!

Thats the example of the reference i run into, i was thinking of a macro to find where the REF starts and delete that cell and everything on down.

Thanks for any help,

hi.i ve got a data table where rows are width and columns are hight. how do i
get data from the cell that matches ceartin column and row,? I use vlookup,
or index, and IF. but IF can be used only 7 times, i need to use it 16 times.
thanx

Hello All,

I would like to search Column C for an instance of the text "Std. Residual", then cycle through the non blank cells to the right and run some formatting code:


	VB:
	
For row_cycle = 1 To 7 
     
     'not signifcant
    If Abs(ActiveCell) < 1.96 Then 
        Selection.Interior.Color = 255 
    End If 
     
     '95% level
    If Abs(ActiveCell) > 1.96 Then 
        Selection.Interior.Color = 49407 
    End If 
     
     '99% level
    If Abs(ActiveCell) > 2.58 Then 
        Selection.Interior.Color = 5296274 
    End If 
     
     '99.9% level
    If Abs(ActiveCell) > 3.26 Then 
        Selection.Interior.Color = 5287936 
    End If 
     
     'move to next cell in row
    Selection.Offset(0, 1).Select 
     
Next 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I then want to search for the next instance of "Std. Residual" in Column C, do the same again, and so on for the entire Worksheet.

Any hints? I am mainly looking for some text searching advice, I think.

Thanks very much in advance.

o

i have a column, and some of the cells have:

My name is //*

I want to find the cells with the //* in it and delete the //* from them.

Not sure how to go about this

I import data from a source and it has 1 blank row between rows of data and
notes. I need a routine that will find that blank row and delete it and move
the remainder of the information up.

I need to make a macro that will find text between "o/" and "/", remove hyphens from the text it found, and then add it to the end of the current cell contents.

I know how to add to the end of current cell contents, but cannot figure out how to grab text between certain characters or replace hyphens and replace with spaces.

What I need to do is have a formula (hopefully) or macro (no idea how to use them yet) that will for instance find items in Column D (MFSPQB) that are marked "x" and then when it finds them will move the information from Column "A" "E" and "G" in the corresponding Row to Sheet 2.

So it would find that D2 has an "x" and move "Firefighter I", "5/29/98", "N/A" (A2,E2, G2) to sheet 2 in A3,B3,C3.

I then want to do the same thing (searching and moving) for each of the colored columns just to other sheets. Any Excel Help for Dummies is appreciated.

******** ******************** ************************************************************************>Microsoft Excel - training3.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEFGHIJKLMNOPQRSTUVWXYZ1CourseMFSPQBDateCert. NumberNPQSDateCert. NumberIFSACDateCert. NumberDODDateCert. NumberVDFPCert. Number2Firefighter IX5/29/1998N/AX6/1/199843709X5/8/2001395983X5/8/2001395983 3959833Firefighter IIX6/15/1998N/AX6/15/199843818X5/8/2001395986X5/8/2001395986X3959864Aircraft Rescue Firefighter (ARFF) 11/17/20037-0402X11/17/200304026X1/27/2004585556X1/27/2004585556 5855565Haz-Mat AwarenessX4/26/2001N/A 5/10/2001398036X5/10/2001395036X5/10/2001395036 6Haz-Mat OperationsX4/26/2001N/A 5/10/2001398040X5/10/2001398040X5/10/2001398040 7Haz-Mat TechX1/13/2000N/AX1/14/200061654X5/10/2001398042X5/10/2001398042 3980428Fire Instructor IX6/16/1999N/AX6/16/199954780X6/29/2005837521X6/29/2005837521 8375219Fire Officer IX1/22/1999N/AX1/22/199946345X6/29/2005837522X6/29/2005837522 83752210Fire Officer IIX8/21/2007781077X8/21/2007781077X8/21/20071037556X8/21/20071037556 103755611Driver / Operator Pumper 4/15/20057-46250318-SJ2X4/15/200598-47305200-2X11/2/2005851884X11/2/2005851884X85188412Driver /Operator Aerial 11/23/20032-315304-SJ1X11/23/200398-42304170-1X5/10/2007898504X5/10/2007898504X89850413Confined Space Entry/Rescue 11/17/1999 Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I'm trying to figure out how to get the column numbers for the first and last non-zero cells in a range. It seems pretty easy to get the actual values using some tips I found on here, but getting the actual column numbers eludes me.

Each cell represents a week, with the first non-zero being the start week and the last non-zero being the end week. All cell values are integers.

Hi:
New to forums, but could really use your help in VBA. I've created a table in an Excel 2003 spreadsheet that uses complex "if" formulas to plot data on basically an x and y axis (much like a chart). Now I need to join only the cells with a value in it with a black line, much like a line chart. I can't figure out the code to look up the first non-blank cell in each column, and then to draw a line to the next column's first non-blank cell. Would really appreciate your help!
Thanks.
Jane.

I need help in copying and pasting non-contiguous cells in the same cells in the target row range as in the source range.
eg: Column B has B1, B4, & B5 cells with values
Column A has A1, A4 and B5 as blanks
I am filtering column A on blank cells, then selecting the column B cells B1 to B5 and choosing "goto active cells only" then copying that range and pasting it in Cell A1 but I need the non-contiguous cells from B to paste to A.
I understand that excel does not allow non-contiguous cells copy and paste.

Any help will be greatly appreciated. Currently I am doing this function manually and is taking quite a bit of time.

Asgi

Hello, maybe someone can help me. I've recorded a macro that goes through 5 sheets and deletes blocks of cells, but when i run it, sometimes it deletes cells that I did not delete while recording the macro... I've tried recording the macro many times, deleting all macros and starting over.

I don't know how to program the macro, just how to record one.

Also, does anyone know how to protect cells from being altered? there are cells that calculate totals and never need to have data entered by users. I'd like these cells to contain formulas and display the calculations, but if by accident a user clicks on one of these cells, they cannot change the contents.

Anyone have any tips???

thanks,

chris

Hey all, I'm a beginner with vba and macros and did a search, but couldn't find anything on this.

I have 4 columns in a spreadsheet that I pasted from two different sources. (see attachment)

Column A is "CC DATE" and it is the date the credit card transaction was processed.
Column B is "CC AMOUNT" and it is the dollar amount of the transaction.
Column C is "BANK DATE", it is the date the credit card transaction was deposited into the bank.
Column D is "BANK AMOUNT" which is the amount that was deposited.

It sometimes happens that a credit card transaction does not get deposited into the bank for whatever reason. I have to find when this happens by comparing the amount in Column B to Column D (they should be exactly the same), and also comparing the date in Column A and C (they should be no more than 3 days apart.)

I am looking for a macro that will highlight the date and amount in Column A and Column B for any instance that (1) There is no exact match between Column B and D AND (2) Column A and C are 3 days or less apart.

Or perhaps someone even has a better way to pick out the non-matches.....

I hope I explained it well. Let me know if you need more! Thanks!

I’m working on this excel file for professional purposes where I need to create a function that runs through two different columns in a Table A, and compare it with cells in a column in Table B, then returns data from another column in Table B.

If you’re starting to feel somehow dizzy, I will try to put it in a simple way.

What do we already have?

1. Table A that has a buyer_id column, and a seller_id column.
2. Table B that has a client_id column and a client_name column.
3. Table C, where the function should be inserted to return the client’s name.

What is needed to be done?

Insert a function in Table C that does the following:
1. Check the buyer_id and seller_id in table A.
2. Check client_id in Table B.
3. Find matches between client_id and the other two columns seller_id, buyer_id, if there are ones.
4. If a match is found, then return the corresponding client_name from Table B.
5. Else, return FALSE.

I have came up with a certain formula of “ifs” and “ors”, but the problem was that this formula compares only cells belong to the same row, i.e. cell A1 in Table A with cell A1 in Table B. where it is highly likely for cell A1 in Table A to match cell A16 in Table B.

Is there anyone in here who can lend me a hand in this please?

Thanks in advance.

A B C D
UPCA UPCB INVENTORY RESULT
55 55 10
56 57 9
57 56 8
90 80 4
80 90 3
17 15 2

I need a formula that will find matches between column A+B then Record the
data that is in Column C in D. B+C are related fields (fixed)


No luck finding an answer? You could always try Google.