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

Free Microsoft Excel 2013 Quick Reference

Comparing And Copying Text In 2 Worksheets

I have a problem below and i need an answer ASAP as i cannot run my system untill this is fixed.

Look @ code information in "Sheet 1" Column A
Go to "Sheet 2".
Search "Sheet 2" column b for the informaton in "sheet 1" column A
If you find it (eg codes match) copy the information in "sheet2" columns b to y into sheet 1.

As you can see from the original information I sorted columns b to y by the supplier field and knocked all the information out of order.

There are 5000 rows in the full worksheet, I have just cut this to the minimum. Also I cannot change the order of the information in sheet 1 column a

The result is that the information in

thans in advance.


Post your answer or comment

comments powered by Disqus
Hy!

I have a huge ammount of exported data, and one cell contains the category name and the category path too. I want to write a macro, which can find the category name, copy and paste it to an other cell, and the same for the path too.

Example:

Cell looks like this:
Category Name: ProductNameSomething, Category Path: Materials/Alloys/Something

And I need the macro to copy the ProductNameSomething and Materials/Alloys/Something to other cells (wothout the "Category Name:" and the "Category Path:").

I would really appriciate some help, how can I select and copy text in a cell after a specified word? Or is there any easier solution?

Compare column A in 2 worksheets and if the same copy the data from column B,C,D etc. over to the firs worksheet.

What I have is an order form and when you input the code in column A of worksheet1 I want it to compare it to worksheet2 and IF the same copy over the rest of the column's with description, product price etc.

1. I am looking to compare between two cell in different worksheets, and copy the result in a different worksheets.
Worksheet one "Inventory" has unique item# in one column, Worksheet two " orders" has duplicate item # in 1 column
a. I want that the macro do a mach-search on item# column in inventory worksheets it my referent worksheets, if the mach-search is true I want to copy the row that contains the cell item# in orders to the new worksheets, delete the row after item# from orders worksheets was copy in the new worksheets, and continue the mach-search on the next row on item# from inventory

Hi there,

i am looking to compare 2 spreadsheets. My systems report against a suppliers system report who holds stock on my behalf.

what i am looking to find out is if there are any differences in the 2 spreadsheets. it would be good to you a conditional format on the extra materials in the supplier spreadsheet and the extra materials in my spreadsheet.

if this possible. Currently takes me hours to find the differences and really is a hardship.

Appreciate any help you would be able to give me.

Kind regards

Paul

If cell H of worksheet A = cell E of worksheet B (contain words)
and
If cell J of worksheet A = Cell H of worksheet B (contain numbers)
and
If cell K of worksheet A = cell I of worksheet B (contain numbers)

then copy cell O of worksheet A to cell L of worksheet B (contain numbers)

(put in other words
if H2, J2, K2 of worksheet A = E1, H1, I1 of worksheet B
then copy O2 of worksheet A to L1 of worksheetB)

if H3, J3, K3 of worksheet A = E5, H5, I5 of worksheet B
then copy O3 of worksheet A to L5 of worksheetB)

i need the macro to match and copy for the whole worksheet of A and B
Data from worksheet A is only to be used once
_______________________________________________________________
this is what i have but it doesnt seems to copy anything..

Dim sh1 As Worksheet, sh2 As Worksheet
Dim j As Long, i As Long, lastrow As Long
Set sh1 = Worksheets("Worksheet A")
Set sh2 = Worksheets("Worksheet B")
lastrow = sh1.Cells(Rows.Count, "A").End(xlUp).Row

For i = 2 To lastrow
 j = (i - 2) * 4 + 1
 If sh1.Cells(i, "H").Value = sh2.Cells(j, "E").Value And _
    sh1.Cells(i, "J").Value = sh2.Cells(j, "H").Value And _
    sh1.Cells(i, "K").Value = sh2.Cells(j, "I").Value Then
    sh1.Cells(i, "O").Copy sh2.Cells(j, "L")
 End If
 j = j + 4
Next


Hi All,

I am new to Excel Macros and need experts Help, I was stuck up at Compare and copy data from one sheet to another sheet in Excel 2007.

I have a excel workbook which contains 3 sheets(sheet1 , sheet2 and sheet3). Sheet1 is master sheet of sheet2 and sheet3 means it will get data from these 2 sheets, so sheet1 columns are combination of sheet2 & sheet3 columns.

sheet2 & sheet3 are linked with different external sources and when ever changes will occur in external sources automatically these two sheets will get updated . so always these sheets are having latest data .

but sheet1 is getting data from sheet2 and sheet3, sheet1 is not updating every time when sheet3 got update ,since we are not automated the connection between sheet1 and sheet3.

our objective of this Macro is , it should compare the sheet1 and sheet3 based on Request Number(it is a unique and common column in both sheets) and needs to copy the whole row corresponding to the request number from sheet3 which does not exists in sheet1 and paste the data into corresponding columns in sheet1. we need to make sure that no duplicate values are occurred in Request Number in sheet1.( example sheet1 have 10 rows and sheet3 have 13 rows, in both sheets 10 rows are identical and remaining 3 rows added newly in sheet3. now we need to copy those 3 rows into sheet1 into corresponding coulmns)

here i have attached the macro code, it is working fine up to some content but not satisfying my requirement.

Any help would be highly appreciated.

thanks in advance.

Regards,
Mallesham

Hi Everyone,

I need some help with writing a macro to compare the unique text in two columns.

For example, I have 3 tabs in the spreadsheet:A, B and C

In Tab A, I have some groups of data
CURRENT_DATECELL_NAMEPORTFOLIO_CODE08_06CashCash08_06BondOffset08_06Bond11108_06Bond11108_06MortgageDDD08_06MortgageDDD

In Tab B, I have some other groups of data
CURRENT_DATECELL_NAMEPORTFOLIO_CODE08_06CashOffset08_06BondOffset08_06BondOffset08_06BondOffset08_06Bond11108_06BondDDD08_06BondDDD

I need to
1) get a list of unique portfolio codes from each of the two columns
2) sort the lists in aphabetical order
3) compare the 2 lists to see if there is any difference between the portfolio codes
4) If they are different, ouput an error msg.
5) If not, put the unique list in column A of Tab C.

Your help will be greatly appreciated!

Angela

Hi

let me explain more clear

how can excel find automatically a given text in all worksheets (i
there is any) of a given workbook and copy the row where this text i
to another worksheet.

should be easy but I can't

sohrab hashem

--
hm
-----------------------------------------------------------------------
hme's Profile: http://www.excelforum.com/member.php...fo&userid=1593
View this thread: http://www.excelforum.com/showthread.php?threadid=27415

I have a macro that copies cells from another worksheet in a loop. The
macro switches between sheets many times while the macro is runnin
because I am activating one sheet, then copying to another. How can I
copy cells in another worksheet without first activating it? Here is
the loop that I am now using:

If Not c Is Nothing Then
firstAddress = c.Address
Do

Sheets("Files").Activate
range(c.Address).Select
Selection.Resize(1, 6).Select
Selection.Offset(0, -2).Select
Selection.Copy
Set rng = Sheets("Directory").Cells(Rows.count,
2).End(xlUp)(2).Offset(0, -1)
rng.PasteSpecial xlValues
Set c = .FindNext(c)

Loop While Not c Is Nothing And c.Address <> firstAddress
End If

I tried using Sheets("Files").range(c.Address).Select but I get a
"select method of range class failed" error. Anyone got any ideas?
Thanks.

Dear,

Actually I'm doing translation on some language files. The files look like this(I have also attached an example of the files) :

File A:
$actioncode = array
(
0 => 'Back to Forum',
1 => 'View Index',
2 => 'View Boards',
3 => 'View Posts',
5 => 'Register',
6 => 'Login',
7 => 'Use CP',
8 => 'My',
); File B:
$actioncode = array
(
0 => 'ص̳',
1 => '̳ҳ',
2 => '̳',
3 => '',
4 => 'sth';
5 => 'עΪ»Ա',
6 => '¼̳',
7 => 'ʹÿ',
8 => 'ҵ',
); Wish to know, is there anyway that can compare and replace the text in 2 files which will do matching like this:

File A
xxxx=>'aaaa', File B
xxxx=>'bbbb', if 'xxxx=>' of File A =='xxxx=>' of File B,
then xxxx=>'bbbb'; will excel help in doing this?

p/s: Sorry, I got no idea where can I ask this question, hope you guys can give me some suggestions...

Thanks in advance!

I have a worksheet with 2 columns of data (non-numeric), I need to count the
occurences of each row in column 2 in column 1. The data in column 2 appears
once and in column one it may appear 1-many times. eg data is like this,
(column 3 is the requested count of column 2 in column 1)

abc abc 1
abcd abcd 2
abcd acd 3
acd ...
acd
acd
...

There's 10,000 rows in column 1 and 3000 rows in column 2

Hi,

what I am trying to do is to write some macro code to 'Find and Replace' certain text strings in each of my work sheet and replace with the correct text e.g. the macro will look in each worksheet in turn. find the text if it exists and replace with new text.

I have tried writing the code below but it appear only to work on the active worksheet

Dim mySheet As Worksheet

For Each mySheet In Worksheets
      
Cells.Replace What:="Test Me out", Replacement:="That Seems To work !!", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


Cells.Replace What:="Test this as well", Replacement:="That Seems To work as well !!", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Next mySheet

End Sub

Any help would be greatly appreciated

Excel version 2003

Many thanks

Rob

I've got a problem with some data I want to compare and copy, from one xls-file to another xls-file. That I hope someone can help me with.

I've made a macro, that can copy the data I want copied. But now there's more then one row to copy, and it's not sure that all the data need's to be copied. So the macro must be able to compare the data, and if the data is the same in the given row, it should not copy that the data, but continue to the next row. Until al the rows a compared.

Here is det code I've made. (This just copies the data.):

Sub Bank1Slot1()
'
' Bank1Slot1 Macro
' Macro recorded 17-11-2008 by Godfasher
'
' Keyboard Shortcut: Ctrl+a
'
    Workbooks.Open Filename:= _
        "C:Bank1.xls"
    Sheets("Slot1").Select
    Rows("6:6").Select
    Selection.Insert Shift:=xlDown
    Windows("PVE LAB TEST OVERVIEW.xls").Activate
    Range("D5:H5").Select
    Selection.Copy
    Windows("Bank1.xls").Activate
    Range("B6").Select
    ActiveSheet.Paste
    Range("H6").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
    Range("H6").Select
    Selection.NumberFormat = "0"
    Range("C6:G6").Select
    Range("G6").Activate
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("B6").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A1").Select
    Sheets("Slot1").Select
    Range("A1").Select
    ActiveWorkbook.Save
    ActiveWorkbook.Close
End Sub
Looking forward to hear from you.
/Godfasher

Hello all, once again,

To get straight into it.
I have the requirement to compare and add Info between 2 workbooks, they both have the same customer No. and I thought of VLOOKUP, but in both sheets there are doubles, and of cause I require for the second instance from sheet1, the Info from the second instance from sheet2.
At the moment I always receive the info for the first instance, is there a way to tell VLOOKUP if second instance in sheet 1 then second instance sheet 2?
or must this be done by code? if so could someone put me on the right track.

Thanks in advance

Ray

compare and copy from ANAGRAFE to SPORTELLI
If in the sheet sportelli not is present a code in anagrafe copy this in the sportelli and put this in the bottom of sportelli

A little bit about the program:

1) I have a Master worksheet that combines/appends data from 5 worksheets. The data in the 5 worksheets is cleared on a daily basis once data has been transferred (using a macro) to the Maser sheet.

2) I use the Match and Index formula for the 5 sheets. Column A "Symbol" being the primary value

Problem:

I have Matching column headings "Date" in 2 worksheets, I can only use date data from one of the sheets that contain the date column data.

Here is the formula I use in the master to accept data from the worksheet


	VB:
	
(Sheet1!C2=0,"",Sheet1!C2) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Sheet1 has the column heading "Date" that is linked to Master Worksheet column Heading "Date"

Is there a possible way to re-write the formula where It can accept data from either sheets instead of only 1 sheet?

Here is an example of what happens

Going back to the fact that I have 2 date columns in 2 worksheets
Lets say for example;

I have Symbol AAA in Sheet1
I have Symbol ZZZ in Sheet2
I have Symbol GGG in both sheets

Depending on the formula, I can only get date from 1 sheet which means,

If I use this formula in master column

	VB:
	
( sheet1!C2=0,"",sheet1!C2) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
then I will get date from sheet1 but not sheet2.

so I will have, after transferring data:

Symbol ...Date
---------------------
GGG ..... Sep 16 2006
AAA ..... Sep 16 2006
ZZZ ..... I WILL GET A BLANK

I need help to re-write the formula so it can allow transfer from both worksheet dates into the Master column "Date" instead of having 1 sheet transferring data.

Thank You Kindly,

Ravi Matharoo

Hello all,

Is there a way to copy everthing in a worksheet (including objects) and paste it into another worksheet?

In addition, will the objects show up in the same place in the new worksheet as the original?

I usually use the following:

Code:
But this doesn't seem to pick up objects...

Hi,

Is there a way to automatically copy text in a cell (say A4 = "File Name: Test 23 (July 16, 2007).txt") into a text field in a graph?

I have a spreadsheet with multiple graphs.

I dump numbers into the spreadsheet and it automatically adjusts the graphs.

I can't figure out a way to create titles in the graphs that are unique to the data.

Right now I have to copy and paste the text from the spreadsheet into each graph - 25 graphs in total.

Any suggestions?

Thanks.

Enter numeric as text in Excel worksheet should not create error or warning.
If I specifically enter a ['] preceding the behavior as I've explicitly
stated it should be that I want this value to be treated text in the
worksheet.

Wish of the day: provide a simple way to make your spreadsheet behave with
the elegance and simplicity of Lotus 123.
--
- evansight

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Greetings,

I have 500 .csv files that I need to edit as they are a backup of a database which at the end is being moved to a new device. In this process I need to:

- Take column A and format them as numbers(currently set as text) with 0 decimal places
- Take Column E,D, or F and any text in there needs to be cut and copied to the end of the text in column C

I was able to use notepadd++ for the other tasks which was a simple find and replace for all 500 workbooks but I do not think I can use it for the rest. In notepad++ I did a find for a 10 digit character which is on every other line and replaced it with the new number. I then did a search for any other text (,,,,) and had it deleted. The only thing it would not do was delete all the extra columns and rows which had no text.

Can you help?
Thanks!

Enter numeric as text in Excel worksheet should not create error or warning.
If I specifically enter a ['] preceding the behavior as I've explicitly
stated it should be that I want this value to be treated text in the
worksheet.

Wish of the day: provide a simple way to make your spreadsheet behave with
the elegance and simplicity of Lotus 123.
--
- evansight

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

Hi,
I work for a school and need to compare and combine data from 2 worksheets. I have to do this every month and currently it takes far too long and have been unable to find anyone that can help me.
The data in each worksheet is from different programs that I need to combine. The first worksheet contains student mailing address information and the second details of payments for a specific purpose. Both worksheets has common data that is the student number. We have around 960 students and I can have anywhere between 400 and 20 students paying within any given month.
Below is an example of what I need, Sheet3 being the end result. I don't care if it is easier to combine both sheet1 and sheet2 completely and need to delete columns or move anything around (that takes no time).
I have tried to research how to do this but as I have no programming experience I have been unable to get my head around it. So if anyone can help me I would be most grateful.

I have a table and am using =VLOOKUP(B3,Table2,2,FALSE) to automatically fill in descriptions for products. Only thing is, I have bolded and underlined text in the description and when VLOOKUP locates the information it appears as regular text. Help...

Hey everyone,

I am looking for a VB macro that searches for a cell which is opposite another cell called "Totals" in Multiple worksheets and copies it in a row format on an existing worksheet.

Too add to the complexity the Cells Worksheet name has to correspond to a cell in the same row.

So basically you press a button. The macro checks the worksheet reference on the summary worksheet, goes through all the other worksheets in the workbook, if it matches the worksheet reference to an existing worksheet it looks in that worksheet for the cell opposite TOTALS and copies it to a cell opposite the worksheet reference in the summary sheet.

Can anyone help me out?

Thanks for your time


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