Windows Support Forum

Creating Formulas in rows from Data in columns

Q: Creating Formulas in rows from Data in columns

I have data in columns - starting A Data columns Formulas Creating from in in rows - P and another set of data from A -P I have various formulae using that data starting in A -A I wish to include the second row of data the A -P into a second set of formulas from B -B Filling right doesn t work because if the formula in the A column is something as simple as quot A C D quot when I fill right the formula in the B column is quot B D E quot - when I NEED it to read quot A C D quot I realize there is probably a simple workaround for this - in fact I COULD just rekey the formula the reasons I don t are the formulas are more elaborate than I listed there will be many more rows of data at least more - Creating Formulas in rows from Data in columns so rekeying the formula for EACH row of data would be time-consuming and it will eventually move the formulas off the same visible area as the data I m currently combatting that by having the panes frozen Is there any way to accomplish what I m trying to get done Chris nbsp

Relevancy 100%
Preferred Solution: Creating Formulas in rows from Data in columns

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link (This link will automatically start a download of Reimage that you can save to your computer.)

Relevancy 109.65%

I have data that repeats hundreds of times. I am trying to put each string of data on 1 line.

f-name(A2) initial(B2) l-name(C2) ID#(D2) DOB(E2) Street(F2) company(G2)
City(F3) company#(G3)
Home Phone(F5)
Work Phone(F6)

After the data is moved to one line I would like to delete the lines that previously housed the information. The company# can be deleted. I have never worked with macros. I would appreciate any help given.

A:Solved: move data from rows and columns to other rows and columns

Relevancy 105.78%

Hi all,

this came up in conversation recently and I thought I knew the answer but was proved wrong.

when you insert a row (or column), it sometimes (but not always) copies the format from the preceeding row (or column).

what are the criteria that determine this?

I thought it was based upon something like if the preceeding 5ish rows were all the same format but it's not that!


Just realised I don't know how to add a link to a previous post!!
btw I used the macro in the posting 11-Aug-2004 "Insert Row Automation" which solves the above nicely (thanks Anne), but I'm still curious for the above criteria.
btw the 'InserARow' macro needs a row test at the beginning to check that you aren't insering above row 1 'cos there aint a row 0 to copy down from!
Relevancy 96.32%

I have been working on a project for a few weeks and I could really use some help I m attaching a dummy file example that is an example of how my XLS comes to me each month I need a Solved: to delete specific XLS Macro move to columns rows/columns rows. and macro that leaves me with the second file example If the macro could just grab the Student name then put the following info for each student all on row that would be perfect Child s Name Date In Time In Rounded Time Out Rounded Sometimes for some reason there is a student with Solved: XLS Macro to delete specific rows/columns and move columns to rows. a Cont after their name I need that row deleted and just pull the times up onto the same row as other times The example file might make sense Here are the steps I am doing manually that I hope a macro could do for Solved: XLS Macro to delete specific rows/columns and move columns to rows. me Un-merge cells B Un-merge any cells with a child s name such as Doe Jane Delete any row with a childs name that has Cont after it such as Doe Jane Cont Delete rows - Delete columns A C F H I J M N O and P Manually move all info up to one row per student Delete all empty rows below See example as the completed file but I only moved a few of the rows per student for the sake of time Is there any way to do all of this with a macro nbsp

A:Solved: XLS Macro to delete specific rows/columns and move columns to rows.
Relevancy 95.46%

I started out with the two previous posts about macros (post 1, post 2) that move rows into columns, but I'm having a little trouble getting it to work for what I need. First, I'm trying to do a data merge into Word, but in order to get the data merge to work/look the way I want it to, I need the XLS data to be in columns.

I'm attaching an example file that has one worksheet of the sample data (though my "real" data has about 300 rows) and the other worksheet is an example of the outcome I need.

Is there anyway I can do what I need? Exporting into another file or another worksheet within this file would be fine.

Thanks in advance.

A:XLS Macro to move data to columns, delete empty rows, delete duplicate columns
Relevancy 92.88%


I have imported sales data in excel. The information lists in along two rows with mutliple columns. I need the information to be in one row under various headings. I have attached a link to a sample to explain this properly. I think I need to use a macro but I don't understand enough about them to make it work.

Any help would be much appreciated.

Relevancy 92.02%

I have a very large spreadsheet (i.e. too big for pivoting and transposing wouldn't work) which looks something like this:
Col. ACol. BCol. CCol. DCol. ECol. FCol. GEntry1Info124562131654632435435Entry2Info150256874654242454654Entry3Info2221333333336546548463
I want it to look like this (i.e. flip Col.C through Col. G and line it up with Entry1, then do the same for Entry2, etc.):

Col. ACol. BCol. CEntry1Info12Entry1Info456Entry1Info2131Entry1Info6546Entry1Info32435435Entry2Info150Entry2Info25Entry2Info6874654Entry2Info242Entry2Info454654Entry3Info222Entry3Info133333Entry3Info333Entry3Info654654Entry3Info8463

Please help!

Relevancy 92.02%

Hi A does anyone know how to search for the text content Searching Rows/Columns EXCEL Data in of a cell through a column and then return the value content of the cell of a certain other column of the same row where the text content was found e g A B C D T XY TP YZ TP ZY P YX TP YY FORMULA TP ZZ gt gt FORMULA EXCEL Searching Data in Rows/Columns quot Look for string TP in column A other than in EXCEL Searching Data in Rows/Columns row and return the value of that row in column C C YZ quot NOTE There might be more than row with the string TP in column A - I would need the sum of all results in column C where the FORMULA finds this string B I also have a similar problem with another worksheet It would require a formula which searches through a whole column for certain names e g quot Donna quot and then returns the value of another column in the same row where it found quot Donna quot the value again is not in a column directly EXCEL Searching Data in Rows/Columns adjacent to the column where quot Donna quot would be found This is fairly identical to problem A except that the search string would be unique only result and it doesn t have to take into account and disregard if the string was found in the same row of the FORMULA the search column would be in a different worksheet from the FORMULA worksheet Thanks for any suggestions Cheers from New Zealand Andreas nbsp

Relevancy 90.73%

We have a survey package which is great, but it out puts the questions numbers over the columns and each new row is a response, however our calculations are setup for the question numbers to be the rows and the responses are the columns. Is there any way to swap these values around in this fashion? Thanks for any help

A:Excel: swapping columns and rows layout of data?

Howdy. You can copy the data, then Paste Special > Transpose
Relevancy 89.87%

I'm a novice excel VBA macro user. I've manipulated already created macros, but I'm not sure how to create one from scratch. Can someone please help me create a macro.

I'd like to move the values highlighted in yellow to the cells highlighted in green...and then delete any rows that are empty.

Thanks in advance for the help.

Relevancy 89.87%

..... and I want to convert the lot to 2 decimal place numbers.

Excel has several methods, but none seem to work for conversion in bulk. Working on futher cells from a suitably formatted one does not work.

Is the only way to solve my problem to manually enter the numbers elsewhere, clear the original cells, and then cut and paste back to the original cells?

Help please!

A:Number data in 10 columns and 25 rows pasted from other source is text

Need more info about the source text file. When opened in Word and Show All (the paragraph mark) is enabled, what does the text file look like?

See this thread from a couple years ago and see if it gives you any ideas:

Please Help - Data in Text File to be arranged in Excel in a specific

Relevancy 89.01%

quot Macro Creation Request for Moving Data from Rows to Columns in a Non-Conventional Way quot Hello all Hopefully somebody can help me with the following problem please see the attached document Presently I have an Excel document containing variables - per country in rows for label through label x However I need the table to contain the country level information as Columns Way Data Request Rows Moving for in from Macro Non-Conventional Creation a to columns Macro Creation Request for Moving Data from Rows to Columns in a Non-Conventional Way for each label so that there is only one row per label For example instead of row containing label argentina variables - then row containing label arab emirates variables - I need the table to contain row label columns argentina variable argentina variable etc then arab emirates variables arab emirates variable etc Also the countries in the table that do not have information for a given label fall at the end of the list for each label instead of in alphabetical order Macro Creation Request for Moving Data from Rows to Columns in a Non-Conventional Way but they need to be placed in Macro Creation Request for Moving Data from Rows to Columns in a Non-Conventional Way alphabetical order when quot columnized quot I have provided the arab emirates as an example Alphabetically it comes before Argentina but in the list it comes after because it did not contain information for those given labels Since information per country will vary from label to label the countries must remain in alphabetical order in the columns regardless of whether information is provided or not per label Is there a macro that may be written for this Thank you so much in advance for taking the time to read my post Happy day nbsp

A:Macro Creation Request for Moving Data from Rows to Columns in a Non-Conventional Way
Relevancy 87.29%

I need a visual basic macro for excel that will do the following From sheet ColumnA Select rows until sheet columnA value does not start with REC as the no of rows is unbalanced sometimes rows sometimes rows sometimes rows etc copy selected rows and transpose to sheet columnA to however many rows were selected Repeat until end for 2002 to in new Visual columns Excel data unbalanced Basic rows sheet using Macro last row My real table has rows and the rows are unbalanced I am attaching a test file Please help me and thank you for your support and time Tech Support Guy System Info Utility Visual Basic Macro for Excel 2002 columns to rows in new sheet using unbalanced data version OS Version Microsoft Windows XP Professional Service Pack bit Processor Pentium R Dual-Core CPU E GHz x Family Model Stepping Processor Count RAM Mb Graphics Card ATI Radeon HD Series Mb Hard Drives F Total - MB Free - MB G Total - MB Free - MB H Total - MB Free - MB Motherboard MICRO-STAR INTERNATIONAL CO LTD G M -L V MS- Antivirus None nbsp

A:Visual Basic Macro for Excel 2002 columns to rows in new sheet using unbalanced data

Can you also attach a second workbook showing how the data should appear after the macro is run?

Relevancy 83.85%

I do a weekly report that I base on results that I get with search done in Internet Explorer I can then cut and paste these results into an Excel spreadsheet moving unwanted Rows, Question: rows Solved: Excel to new Removing columns some but I need to delete most of the information whole rows and reorganize the remaining rows by taking the even numbered rows and putting them into the B column then delete that Solved: Excel Question: Removing unwanted Rows, moving some rows to new columns Row as well I have included some images that should help Solved: Excel Question: Removing unwanted Rows, moving some rows to new columns A few more items The information that I paste into the excel spredsheet may contain up to but no more than lines from the original copying source in Explorer I m not sure how many lines that translates into Excel After every records in Explorer there is a quot top quot link that gets copied which needs to be taken into consideration when deleting the extra rows entirely Another way to look at this is that I ONLY want to keep the rows that have the digit number a space then more digits as well as the rows that contain the price with the For instance the first record in my example I ONLY kept amp The price will ALWAYS have a and the first set of number will ALWAYS have digits first no letters Attached is a jpg that shows the various steps the last screen shot has a few of the cells highlighted in the upper left hand side Those highlighted cells is all of the information I need and how I need it presented from the first search records the MLS number and the price Thanks nbsp

A:Solved: Excel Question: Removing unwanted Rows, moving some rows to new columns
Relevancy 72.67%

Hi There

It has been quite a while since I asked for help. I wonder if the following is doable as an Excel macro. The attached is only a demo with only 2 fields and a few rows of data. The real worksheet has more fields and around 3,000 rows.

Sheet 1 contains the data in its initial state and sheet 2 contains data after the macro is run. You will see from sheet 2 that the field labeled QUANTITY determines the number of rows to insert and copy the correct data into those inserted rows. I want the entire row to be copied down as opposed to only the data, since I have more columns than shown here. Is this possible to do? Can anyone help me with writing a macro, since I have over 3,000 rows to do.

Thanks for all your help. You guys are fantastic.


A:Inserting specific number of rows in Excel and copying data in those inserted rows
Relevancy 72.67%

Hi I am trying to copy and insert rows based on a number in cell E. If the number in cell E is 4, I would like to copy 3 additional rows beneath the original and then also number the 4 rows into column F consecutively for each group. I am attaching a spreadsheet of before and after. Sheet 1 is the before and sheet 2 is the after. Can anyone help me with this. I have tried another code but it not work properly. I did find one that would put blank rows in but I cannot get past that. Any input is greatly appreciated.

Thank you


A:insert rows based on number in cell and copy the data down into the new rows

Hi Peg

Try this solution and see if it is ok for you. I've put in some code to help prevent the running of the code multiple times on the same sheet. If you don't need this safeguard you can delete the block of code that does this.

Also, make sure and make a backup of your file before running this - just in case.
Relevancy 72.67%

I have a spreadsheet with lots of complicated formulas however when I insert a row in the middle of the speadsheet it doesn't automaticaly insert the formulas as per above. Any ideas.......

A:Excel 2007 - Inserting Rows and Formulas

you should just be able to drag a copy down
so insert a row - goto the previous row - select the cells - click on the small black box , bottom right hand corner and then drag down the whole selection will copy down

click on the row number of the row above the insert a black box appears - bottom righthand corner - click on box drag down

IF you have set all the formulas correct that should work
Relevancy 71.38%


I do not know much about excel so i need your help.
Attached is a screenshot. Please examine it a bit to know which rows and columns the data responds to.

Now that you done that heres the question:

How can i switch the dates to the top row and the exercises to where the dates are but keeping the readouts of the weight measurements to its corresponding labels?
I looked over it and it seemed like i had to do it manually but i am wondering if there was a way to automatically do it? Without all the hard work

A:Flip the rows and columns

Check out the function =Transpose()

Type into the top cell you want to put the new table (I'll use A32), and using your screenshot values:
In cell A32: "=Transpose(A1:K27)" (don't use ") after you type that in, select starting at that cell the same area for your new table - all the cells.

So select A32 and select to AA42, then press F2, hold down Ctrl & Shift, and then Enter, and it will Transpose the vertical to the horizontal and all the corresponding data.

Transpose is brilliant.

I recommend that you then copy the information from that and use paste special and keep values, then delete the complete rows with the transposed array, its annoying cos you can't change the info, so just turn it back into cell data is the best option.

Unless you want to put it on another sheet, and want it to update from your original data.
Relevancy 71.38%

I have formulas that has the data setup in rows (input). The next sheet is formulas that are in columns referencing these individual rows (output). When column A's formulas are dragged to column B, the references change as expected but to Sheet1!B1 rather than Sheet1!A2 (back to the rows). I think I 've done this before, but can't for the life of me remember how. Help me please and I'll be your best friend! (hey, it's the best I can do).



A:Excel Columns to Rows

Select all the cells involved

Right click anywhere on your selection and from the menu that pops up choose Copy

Click in the cell where you want the list to begin then Right click and from the menu choose Paste special you will now see another menu.

At the bottom right hand side of this menu, Select Transpose.

That's it your list will now run across the page instead of down
Relevancy 71.38%

Copy Rows into columns using VBA1 have a very little experience with VBA, and I would really appreciate any help with this issue. I need to convert rows into columns from sheet 1 to sheet 2.input file Article ID EU-statistics Number of sheets Size of sheet Absorbancy With empties BOM (Empties Bill of Material)1062 a 0 b 0 c1065 a 0 b 0 c1066 a 0 b 0 c1071 a 0 b 0 c1077 a 0 b 0 c1081 a 0 b 0 c1086 a 0 b 0 c1090 a 0 b 0 cDesire output file Article ID 1062 EU-statistics a1062 Number of sheets 01062 Size of sheet b1062 Absorbancy 01062 With empties BOM (Empties Bill of Material) c1065 EU-statistics a1065 Number of sheets 01065 Size of sheet b1065 Absorbancy 01065 With empties BOM (Empties Bill of Material) c1066 EU-statistics a1066 Number of sheets 01066 Size of sheet b1066 Absorbancy 01066 With empties BOM (Empties Bill of Material) c
Relevancy 71.38%

Isn't it possible to freeze a specific row or column, rather than panes?

A:freezing rows/ columns

If you are talking Excel then you can freeze the Top rows and side columns as headings, but I don't think you can freeze individual rows/columns anywhere else on the worksheet.
Relevancy 70.52%

Hi I would like to put names or numbers on the columns and rows Ive managed to get a table to work for me its a set of multiples for the first cell is blank the next headings EXCEL, on i columns?? put Can and rows is the first entry and i put in this case here the rest of the table fills itself in with multiples of Thing is when i print it out i want the columns and rows to have headings Is there a way to do this please The drawing is only aproximate i have to convert to bmp then add in the extra numbers which EXCEL, Can i put headings on rows and columns?? dont change they could be background the colour doesnt matter it is only there to separate the columns and rows from the multiples of Later if its or i would use the same table lt img src quot http forums techguy org attachment php postid quot gt by using the tens and units you get the multiple at the intersection row and column give the result for times There might be better ways but this works ok Regards John nbsp

A:EXCEL, Can i put headings on rows and columns??

I set up the rows and column headings the way you want.
Then I hid the "real" row and column headings.
You can left-click to view it.
Right-click and choose Save target as to save it.
Relevancy 70.52%

I have some Excel files that only take up 50 - 100 rows, but the scroll bar on the side is very tiny (as if I had used the worksheet to the bottom). Is there a way to get rid of these unwanted rows at the end? I've highlighted and deleted them, but they just get replaced. Is there a simple way, withouht copying the data to a new file?


A:Excel - Getting rid of extra rows/columns

Copy and paste the cells into a new worksheet. By default, Excel opens a Workbook with 3 Worksheets. After copying andpasting the info, nad verifying that itlooks the way you want, then you can right-click the worksheet tab at the bottom and delte the old one.
Relevancy 70.52%

how can the contents in cells A1, A2 and A3 be transposed so that they are on the same row, AND always on the same row as B1; and

how can the contents in cells A4, A5 and A6 be transposed so that they are on the same row, AND always on the same row as B4.

a sample worksheet is attached.


Relevancy 70.52%

I remember seeing somewhere that you can convert rows to columns in Excel, where the numbers would be across the top and the letters down the side. How is that accomplished? Also, if there is data in the spreadsheet at the time, will it be converted too or will it just change the tabs?

Relevancy 70.52%

When I attempt to insert a column or a row, I'm getting a message from MS Excel:
"Cannot shift objects off sheet".
Can someone advise me as to what I must do to correct this?
Also, what have I done to cause this to happen?
Thank you for your help.

A:Solved: Inserting Columns or Rows

hi floydcojacket,

I think this is connected with your earlier issue with cell comments.

I'm now sure you must have your Advanced Options, Display Options for this Workbook- Display Objects set to Hide.

That option is in the next block below the Display options for Comments.

Relevancy 70.52%

Hi all. First post so be kind! Bit of a novice at VBA, but i am sure this is possible..
basically, I have a contact list in Excel that I need to format 'correctly'. It is currently formatted with the address on multiple rows, with phone number and email address in another column ( i have attached a dummy file). I would like to 'move' data so it is in correct columns...
Name Address1 Address2 Address3 Address4 Address5 Phone Email

To make matters a bit trickier, the number of address rows varies, so it might be 5 rows or sometimes 3; and not all contacts have an email address (although they would always be in row "2" of a contact address.

Many thanks in advance for any advice


Relevancy 70.52%

I had a similar post and thought it I understood the solution, but did not. Solution called for Pivot tables and it's not quite working for me. Just wondering whether a VBA file would better serve the purpose. Please look at the attached file. Many, many thanks for the kind help. (Running Excel 2003 on an XP- SP3 machine.)

Relevancy 70.52%

I am really new to Excel (2003) and have read several similar posts to mine below; but cannot adapt the existing programming to accomplish the task. I wish to convert a data in “Book A” to appear like that of “Book B”
This will be an almost daily repetitive task. Please refer to the attachment. Thanks for looking. Any thoughts on the subject will be greatly appreciated.

System information:System Board: ECS K7S5A Chipset: SiS 735
CPU: AMD @ 1200 MHz
System Memory: 4x1024MB (DDR SDRAM)
Bios: AMI (11/21/01)
Video: onboard
Audio: Creative SB Live!
Op System: Win XP - Pro SP3 with all updates

Relevancy 69.66%


I need help in Excel copying columns and paste it in rows.

Ex: I have Columns A and B.
Column A Column B
5.78% 4.51%
23.60% 32.50%
14.17% 16.41%
32.12% 54.16%

Now output should be in one row.

Row1 5.78% 4.51% 23.60% 32.50% 14.17% 16.41% 32.12% 54.16%

A:Copy columns and paste in rows in Excel

sub test()
dim rng as range
dim i as long
dim col as long
dim nextcol as long
dim lastrow as long
lastrow = range("A"&rows.count).end(xlup)
set rng = range("A2:B"&lastrow)
nextcol =col+1
for i=2 to lastrow
range(cells(i,1),cells(i,col)).copy cells(1,nextcol)
end sub
Relevancy 69.66%


I have data in the following row format:

0 4591 5 4491 10 4461 15 4441 20 4321

The data are X and Z points so the above example shows 5 pairs of data. I would like to place these pairs of data sequentially in two columns where one column has all the X's and the other has the related Z's. I have many, many rows of similar data that I need transferred into two columns.

0 4591
5 4491
10 4461
15 4441
20 4321

Could anyone help me out with a macro that can do this for me?

Thanks very much in advance.

A:Solved: Excel Macro - Rows to Columns

Welcome to the board.

Let's say the example pairs are in A1:J1.

With A1:J1 selected,

Sub test()
For Each Cell In Selection
If Cell.Column Mod 2 = 1 Then
Range("A" & Rows.Count).End(xlUp).Offset(1) = Cell.Value
Range("B" & Rows.Count).End(xlUp).Offset(1) = Cell.Value
End If
Next Cell
End Sub

gives you actual pairs in A2:B6.

So, what do we need to adjust to make it compatible with your "many, many rows of similar data" scenario?
Relevancy 69.66%

I need to convert rows to columns in Excel like this:

Monday | 1
Tuesday | 2
Wednesday | 3
Thursday | 4
Friday | 5
Monday | 6
Tuesday | 7
Wednesday | 8
Thursday | 9
Friday | 0


Monday | Tuesday | Wednesday | Thursday | Friday
1 | 2 | 3 | 4 | 5
6 | 7 | 8 | 9 | 0

Is this possible?

Well, that didn't display at all like I expected. In short, I need the repeating headers in column A to become one set of headers in row 1 with the data corresponding to each header name below it. Does that make sense?

A:Solved: Excel Rows to Columns with a Twist

Try this :

1) copy the days i.e(MondayTuesdayWednesday Thursday Friday Monday Tuesday Wednesday Thursday Friday
2) Go to the required cell and right click and select "paste special as" then select "Transpose"
You will get the data in this shape
MondayTuesdayWednesday Thursday Friday Monday Tuesday Wednesday Thursday Friday
4) Repeat the same step for other data............
Relevancy 69.66%

I have an excel file with single worksheet. The file is about 4-5 print pages in A4 size. The excel file has titles in first column and 5th row. I want to repeat these titles in every pages when printing. How do I do this. Can I use macros for this.

A:Repeating rows and columns for printing in excel

Relevancy 69.66%

Okay got a convoluted Rows vs - Non-Adjacent Columns Filling Formula spreadsheet going on amp I m not entirely sure how to describe it in words I tried several searches on these forums and on Google but I didn t find anything that was relevant So I m going to apologize in advance if I m re-posting a topic due to my inability to use the appropriate vocabulary to describe this Non-Adjacent Formula Filling - Columns vs Rows problem Running Windows XP Professional SP Excel I ve attached an example spreadsheet Here s the simplest way I can describe it Test is the first spreadsheet I m working with - All the data is already filled in by a vertical organization - This spreadsheet will be updated on a regular basis I m trying to have the data in the columns moved over to another sheet - Horizontal organization This can not be compromised - Absolutely necessary - This is being done by formulas of course - This needs to be done on separate pages i e Jack should have his own page Jill should have her own page - It would be preferable to find a way to use the Fill command as there are more records currently and more coming or at least a way to automate the proper filling Is there any way in which this can be done with merged cells If not then I don t mind doing it with un-merged cells - Merged Cell Version is quot Test quot Sheet - Un-Merged Cell Version is quot Test quot Sheet Both the Vertical amp Horizontal Based Spreadsheets are going to be updated on a regular basis but the first sheet is always going to take precedence basically the nd sheet is going to be working off of the data in the first spreadsheet nbsp

Relevancy 68.8%

I have a flat file that when I read it into excel it populates column 1 of each row with data.
This data is actually a series of 5 fields that I need to have in columns and rows, that is,
column a1 thru a5 I need placed in a1, b1,c1,d1 and e1 followed by
column a6 thru a10 placed in a2,b2,c2,d2 and e2.
The data is balanced, that ism there is data in each of the 5 fields for a "record".
Any help would be appreciated.

Relevancy 68.8%

Hello - I found a previous post on this site that solves (I think) 90% of what I need to accomplish. I have a large spreadsheet that I need to:

· Delete column C
· Delete all rows without account numbers (Acct numbers will always be in column A)
· Delete all rows that have dates in column F other than “00/00/0000”.

The code I found on this site is :

Simple vba code:
lastrow = Range( "A65536").end(xlup).row
For y = LastRow To 2 Step -1
If Cells(y, x).Value = "" Then Cells(y, x).Delete shift:=xlUp
Next y
Set the range to the the column desired, in this case "A"
Lastrow checks the last row and starts from the end up.
Removes all empty rows entirely up to row 2 because row 1 generally is the header
Happy coding
Can anyone help me?

A:Excel VBA Macro to delete rows/columns with criteria

To delete column c, use:
Delete all rows without account numbers (Acct numbers will always be in column A)
If Cells(y, "A").Value = "" Then Cells(y, "A").Delete shift:=xlUp

lastrow = cells(rows.count,1).end(xlup).row
For y = LastRow To 2 Step -1
If Cells(y, "A").Value = "" Then Rows(y).EntireRow.Delete
If Cells(y, "F").Value = “00/00/0000” Then Rows(y).EntireRow.Delete ' Delete all rows that have dates in column F other than “00/00/0000”.
Next y
Relevancy 68.8%

I know you can hide and unhide rows and columns but can you make it so they don't even exist?

I have a just under 2m picture I want to put as a background but when you put the file as a background it tiles it the whole 256 columns and 65,536 rows even if I have them hidden.

This makes my 2.5m spreadsheet over 32 megs in size even with the columns/rows hidden.

I need to be able to see the lines on this above the picture for this project and I can't find any way to do this. I thought I could put it in the background but this pesky problem turned up.

I tried adding more space to the jpg that was empty white space but this too also increased the file size.

Relevancy 68.8%

Hi, I'm new to macro. Hope you can help me with my problem. I've been stuck with this for a week with no result.

I have this table:

001 002 003 004
111 115 119 123
112 116 120 124
113 117 121 125

Let's say, the first row are the StoreID and all other digits below them are the ProductID

I want to convert this table into something like this:
001 111
001 112
001 113
002 115
002 116
002 117
003 119
003 120
003 121
004 123
004 124
004 125

I haven't made any macro before in MS Excel and this one is really getting the breath out of me.

Hope you can help me please.. I'd really appreciate it.

Thank you in advance!

A:Help with Macro in excel - Convert Rows to Repeating Columns

is this just a "one off" thing or do you have many sheets to convert? Because for one or a few sheets, it is not a lot of work to do manually...probably take a few minutes ( I 'll explain if you need)

to automate I can do it, but I need to build some loops and references to refer to the columns and ranges to move to a new's a bit of work.

Please confirm also if it is only 4 columns, and or if the column count varies
Relevancy 68.8%

Hi Again

CodeLexicon gave me a code that worked very well in hiding column A only. Is it possible to make the macro hide all columns with all zeros?? Also I tried to change CodeLexicon's macro to do hide all rows whose columns have zeros but without success. I tried to make the code generic.

Attached is a workbook with the 2 macros. Test the macro that I did out and see that it only hides row A only.


Relevancy 68.8%

In my work environment we are writing test cases and for the test steps we use a word table Its the same columns so we can load them into the Quality Center environment One issue we had was our review tool doesn to macro insert columns and table new rows into Word a t support word but does text documents The format of the table Word macro to insert new rows and columns into a table was kind of messy when we saved as a text document in the sense it would lump all the stuff together with no clean breaks As a solution people making every other row and column blank so there would be a break in the text format Once the review was complete you would have to then go back into that word doc and delete all the blank rows and columns to load into Quality Center Not hard but time consuming with the number of test we would deal with I wrote a quick macro that would delete the header in the table plus all the blank rows and columns Now what we have is we are re using these test and adding to them Currently we are pulling them out of quality center adding the blank rows and columns back in adding our updates sending back out for review deleting the blank rows and columns macro does this load back into Quality Center There may be a better way to go about this but what I am after is this A macro that will insert a blank row and column every other row and column I have been off and on playing with this for a few days but really have gotten no where with it Posted on a few non technical forum and they suggested posting on this site for some help I tried searching and didn t come up with anything like my scenario so hopefully not asking something previously asked Thanks nbsp
Relevancy 68.8%

Hello - I found a previous post on this site that solves (I think) 90% of what I need to accomplish. I have a large spreadsheet that I need to:

· Delete column C
· Delete all rows without account numbers (Acct numbers will always be in column A)
· Delete all rows that have dates in column F other than “00/00/0000”.

The code I found on this site is :

Simple vba code:
lastrow = Range( "A65536").end(xlup).row
For y = LastRow To 2 Step -1
If Cells(y, x).Value = "" Then Cells(y, x).Delete shift:=xlUp
Next y
Set the range to the the column desired, in this case "A"
Lastrow checks the last row and starts from the end up.
Removes all empty rows entirely up to row 2 because row 1 generally is the header
Happy coding
Can anyone help me?

A:Excel 07 MACRO to delete rows/columns w/ criteria

Delete all rows without account numbers (Acct numbers will always be in column A)Click to expand...

If there are no acct numbers, will there be

blanks ""
Space " "
dash -
other ??
Can you provide a sample with dummy data so we can see the data structure?
Relevancy 68.37%

Well..glad I found this forum. I would like to be able to populate a word document from an excel worksheet, with the ability to select (highlight) multiple rows in excel and have those rows transferred to word, but only certain columns

I found almost the right macro here:

Except you can only select (1) row.

Any coding solutions on how to copy a range of rows? This will also require the .Text in the word paste to change....
I see some hope with this code as well, but figuring it out may take me longer than someone that does this everyday.
Thank you.

Relevancy 68.37%

I am un able to get an existing word file to open I can Doc Word Select Populate (Multiple Columns) Rows, Excel from get the following to open a new word file I need this to opoen an existing file Sub export Word row Dim oWD As Object Dim wdDoc As Object Dim TWB As Workbook wsh wsh As Worksheet Set TWB ThisWorkbook On Error Populate Word Doc from Excel (Multiple Rows, Select Columns) Resume Next Set oWD GetObject quot Word Application quot If Err Number lt gt Then Set oWD CreateObject quot Populate Word Doc from Excel (Multiple Rows, Select Columns) Word Application quot Err Clear Set wdDoc oWD documents Add With wdDoc With Range paragraphs Range Text quot PICK UP amp DELIVERY quot amp Chr Font Size Font Name quot Times New Roman quot Font Bold True Font Underline True ParagraphFormat Alignment End With With Range paragraphs Range Text quot PICK UP quot amp vbTab amp Cells row quot K quot Value amp vbTab amp Chr Font Size Font Name quot Times New Roman quot Font Bold True Font Underline True ParagraphFormat Alignment End With With Range paragraphs Range Text Chr amp Chr amp Chr amp Chr Font Size Font Underline False ParagraphFormat TabStops ClearAll DefaultTabStop Application CentimetersToPoints ParagraphFormat TabStops Add Position Application CentimetersToPoints Alignment Leader End With With Range paragraphs Range Text quot NAME quot amp vbTab amp Cells row quot B quot Value amp vbTab amp Cells row quot C quot Value amp Chr amp quot ADDRESS quot amp vbTab amp Cells row quot D quot Value amp vbTab amp Cells row quot E quot Value amp Chr amp quot PHONE quot amp vbTab amp Cells row quot R quot Value amp Chr amp quot MODEL quot amp vbTab amp Cells row quot F quot Value amp Chr amp quot VIN quot amp vbTab amp Cells row quot G quot Value amp Chr amp Chr amp quot WORK TO PERFORM quot amp quot quot amp vbTab amp Cells row quot I quot Value amp Chr amp Chr amp Chr amp quot SPECIAL INSTRUCTIONS quot amp quot quot amp vbTab amp Cells row quot J quot Value amp Chr Font Size Font Name quot Times New Roman quot Font Bold False ParagraphFormat Alignment End With End With oWD Visible True wdDoc Activate End Sub nbsp

A:Populate Word Doc from Excel (Multiple Rows, Select Columns)

Welcome to the forum
You should paste your code in a code clock

[ code ]

no spaces in between the []

[ / code ]

Set wdDoc = oWD.documents.Add

To open a word doc

Set wdDoc = oWD.documents.Open(fullpath and name of the file)

This should do the trick
Relevancy 68.37%

In Sheet one I have some raw data which in sheet two I have picked out the useful pieces and Fill another across sheet in move rows to Excel I problem. need columns have then input into out work forms The problem is that I need to fill the form across column but in the sheet that it is referenced from I need it to take the data from the row EX sheet one a b c a b c a b c a b c I Excel problem. Fill columns I need to move across rows in another sheet need to find a way to automatically get it to do this referencing sheet in column A and then filling to the right but actually having it go down the rows in sheet sheet a b c a a a b b b c c c the idea is that when the software exports to sheet we will be able to print sheet two in the form we need it without having to make any changes I know my explanation is a little crude but if someone could help me I would appreciate it thanks nbsp

A:Excel problem. Fill columns I need to move across rows in another sheet

You could use:


in Sheet2!A1, then replicate that down an across (pro forma style).

Would that help?
Relevancy 68.37%

Ive spent so much time on this, I want to pull my hair out.

I have a pdf file of addresses. I converted the pdf to an excel file (the one I uploaded).

My eventual goal is to convert the addresses into excel, then do a mail merge into word.

I found a tool (via this great site) called ASAP utilities that I can turn the rows into columns, thats easy.

But the problem is, the addresses aren't always the same length. Some labels are 4 rows, others are 6 rows.

Is there any way I can either automatically make the rows 6 each without doing it manually? Or does anyone have any type of suggestion?

Thanks so much for the help

A:Converting excel rows (addresses) to columns for mail merge

Firstly, can I assume that you have permission to use those addresses for your own mailing?

If so you could go back to the originator and ask them to give you a CSV file, or maybe see if the pdf file can be saved as a CSV file.

If so, it would be very easy to bring that into Excel and then convert the file from text to Columns, using the Data, Text to Columns feature.
Relevancy 67.51%

Excel 2000

I have created a calender ( actually a schedule) in excel which has multiple rows columns (A9:O36). The schedule shows where everyone works that day (eg billy on fries, johnny on grill, susie on cashier)

At the bottom I want to create one box which shows what days each worked and where

..................Fries | Grill | Cashier |

I've tried Sum. sumif, countif, if-then even an array...
Can't seem to do it or find the answer through searching
The closest I've come is the array, but I don't know how to make it work for a range of cells that is not a column
Any thoughts would be appreciated.

A:Solved: count instances that occur in multiple rows/columns after criteria met
Relevancy 67.51%

for some reason, this afternoon they began showing only ONE column, on the left side of the folder. the remainder of the folder is blank. see attached screen shot.
thanks for helping, boyd.

Tech Support Guy System Info Utility version
OS Version: Microsoft Windows 7 Home Premium, Service Pack 1, 64 bit
Processor: AMD Athlon(tm) II P340 Dual-Core Processor, AMD64 Family 16 Model 6 Stepping 3
Processor Count: 2
RAM: 2810 Mb
Graphics Card: AMD M880G with ATI Mobility Radeon HD 4250, 256 Mb
Hard Drives: C: Total - 288256 MB, Free - 112291 MB; D: Total - 16683 MB, Free - 2407 MB; F: Total - 99 MB, Free - 89 MB;
Motherboard: Hewlett-Packard, 1444
Antivirus: AntiVir Desktop, Updated: Yes, On-Demand Scanner: Disabled

A:Solved: Folders no longer show rows and columns of icons/thumbnails.

Thanks for the screen shot; makes it very clear what you are describing.

If you select a file it will preview it in the Preview pane (maybe not all types of files). If you'd like the Preview pane smaller and the main area larger drag the divider to the right. If you don't want the Preview pane at all get rid of it (Organize - Layout - remove check from Preview pane).
Relevancy 66.22%

Hello I ve long used a simple macro on various spreadsheets to reset the last used cell Sub Reset Range Application ActiveSheet UsedRange End then Excel 2003 range reset to & delete columns rows, macro Solved: blank Sub This works fine but prior to using it I would have to press CTRL End to see where the current last cell is then manually delete any blank columns and rows appearing at the end of the data by selecting them by their column letters row numbers right clicking and selecting Delete Failure to do this would mean the macro didn t successfully correct the last used cell I would like to add this stage into the Solved: Excel 2003 macro to delete blank columns & rows, then reset range macro I found some code on the web that allegedly removes all blank rows and columns so I pasted it at the top of my existing macro Option Explicit Sub DeleteBlankRows Dim Rw As Solved: Excel 2003 macro to delete blank columns & rows, then reset range Long RwCnt As Long Rng As Range Application ScreenUpdating False Application Calculation xlCalculationManual On Error GoTo Exits If Selection Rows count gt Then Set Rng Selection Else Set Rng Range Rows Rows Solved: Excel 2003 macro to delete blank columns & rows, then reset range ActiveSheet Cells SpecialCells xlCellTypeLastCell Row End If RwCnt For Rw Rng Rows count To Step - If Application WorksheetFunction CountA Rng Rows Rw EntireRow Then Rng Rows Rw EntireRow Delete RwCnt RwCnt End If Next Rw Exits Application ScreenUpdating True Application Calculation xlCalculationAutomatic End Sub Sub DeleteBlankColumns Dim Col As Long ColCnt As Long Rng As Range Application ScreenUpdating False Application Calculation xlCalculationManual On Error GoTo Exits If Selection Columns count gt Then Set Rng Selection Else Set Rng Range Columns Columns ActiveSheet Cells SpecialCells xlCellTypeLastCell Column End If ColCnt For Col Rng Columns count To Step - If Application WorksheetFunction CountA Rng Columns Col EntireColumn Then Rng Columns Col EntireColumn Delete ColCnt ColCnt End If Next Col Exits Application ScreenUpdating True Application Calculation xlCalculationAutomatic End Sub However it doesn t work with my current document - pressing CTRL END after running the combined macro takes you to the same last cell as if you hadn t run it Cells in the blank columns and rows at the end have formatting shading cell borders but no obvious data As a test I also tried deleting the contents of column in the middle of the data selecting column and pressing Delete just leaving the cell formatting in place The macro failed to remove that column too So can someone help me get code that actually works in deleting empty columns and rows and resetting the last used cell Ta nbsp

A:Solved: Excel 2003 macro to delete blank columns & rows, then reset range
Relevancy 62.78%

Hello I am looking for copy data based on criteria into a new work sheet and format it at the same time My data is a database dump with thousands upon do data rows to dump new from How add I worksheet data of thousands of records and for my purposes I want to create a semi-refreshing excel dashboard that will show the data I want to extract Basically I am dumping How do I add rows of data to new worksheet from data dump the database to an excel tab but can also access the database so getting to the data is not the problem What I would like to do is get the data based on criteria and add it to an excel worksheet with a row being created based on the successful criteria of the data Basically my data looks like this DATA FILE TAB Data file sample http i stack imgur com HSL o jpgAnd this is what I want the data to end up looking like One thing to keep in mind is that I can't simply populate a worksheet as I need to be able to create new rows once good data has been filtered Formatted Worksheet Formatted Worksheet http i stack imgur com twXGS jpgThanks for your help in advance

A:How do I add rows of data to new worksheet from data dump

I'm not quite sure what kind of help you are asking for. Your subject line asks about "adding rows to a new worksheet" yet you posted an image [2] of data that is apparently sorted in some way and formatted with different font sizes and fill colors. In addition, the 2nd contains data that doesn't even appear in the original data set.What exactly are you asking for help with? Are you asking us to help create that 2nd, formatted sheet or are you asking for help with adding rows to that formatted sheet? I also don't understand this line:with a row being created based on the successful criteria of the data.What does "successful criteria of the data" mean?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
Relevancy 61.92%


I am trying to classify and code my inventory items in excel.
My classification sheet contains data in columns which I would like to copy to rows in another excel sheet.
Here is the sample data in ACMOTORS-ATTRIBUTES sheet:

The above sheet has to be converted to columns as in Item_Classificatios_Form sheet :

Can anybody assist me in writing a macro for the same.


A:Solved: Excel Macro to convert Rows from one sheet to columns in another sheet
Relevancy 61.92%

I sure hope someone can provide or direct me to the formula to accomplish the following in Xcel I have a singe sheet consisting of rows of data in columns A person has been maintaining this and we are about to convert it to cvs and install a text file in a CGI Perl program to run on a web site For some unknown reason the person who started and has been maintaining the file has set it up In combine Solved: to Need one into rows columns I multiple multiple column containing Xcel, so that the first Solved: In Xcel, I Need to combine multiple columns containing multiple rows into one column columns contain data that should be all in one column Solved: In Xcel, I Need to combine multiple columns containing multiple rows into one column Here is an example a ham radio call we will say is WW XXX In the first column A row he has entered the prefix quot WW quot in the second column B row he has entered the call district quot quot and in the third column C row he has entered the suffix quot WWW quot I have inserted a blank column quot D quot I can combine for example A B and C with the simple formula A amp B amp C placed in D No problem Now the problem and I m sure you all ready can see it I need to do this with all rows It certainly SEEMS logically that I should be able to accomplish this but durned if I ve been able to do it I have Googled I have Jeeved it you name it I ve tried it I have found some quot close calls quot that almost answer it but not the real answer Obviously I can do this times manually but I d rather find a simpler answer if there is one Blessings to anyone who can provide me with the answer This by the way is for a non-business non-profit amateur radio related web site I have no problem converting the Xcel file to the necessary text Fortunately he didn t use any commas in it Anyone have an idea Thanks much Alabama Ron nbsp

A:Solved: In Xcel, I Need to combine multiple columns containing multiple rows into one column
Relevancy 60.2%

Hi Again everyone;

May I please ask for some additional help.

I wish to copy data (not cell formulas) from F115:K115.
I am "trying" to use the following VB formula;

Range(Range("("f115"), Range("f115").end(xltoRight)).Copy Range ("J10")

Actually, the above VB formula copies and puts something into the 7 cells to the right of J10, but it's all gibberish.

I see that I am copying a mish-mash of cell references and so forth, and not 'just' that data (the values) which are in these cells.

Would someone please tell me how to copy 'only' the values which are in cells (F115:K115) ...
Oh yes, the values in these cells (F115: K115) keeps changing (as I want it to do) but the data does remain in these cell locations.

As always, thank you for your help.

A:Please: how do I copy (in VB) only data and not cell formulas and such

Hi J,
I assume you are talking about VBa and Excel (version (?))
If you want to copy a range of cells just do that.

You could even record the macro and see what it does.

Range("J10")..PasteSpecial Paste:=xlPasteValues

You select the rannge you want to copy and then select the first cell in the target range and paste

Hope this helps, if not, I suggest you put some more information in your post, you know, we are quite good at helping but guessing and asuming, that's quite a different matter
Relevancy 60.2%

I hope someone can help me with the attached spreadsheet. I've given up trying to make it work. What I would like for it to do is:

1 - Give a total of the amount paid by the different Types, and

2 - Automatically list the names of the different types in their respective sheets.

It seems as if it is not possible to do this, but if there is, I would be hugely grateful.

Thanks in advance.

A:Solved: Excel Data Formulas

Have attached a sheet which is a starter to what you are after.
I am sure some tweaking by an expert will get you what you want.


P.S. tried to edit without luck. When in tabs A,B,C highlight cells A2:A12 and then select sort Z:A. This will clear out the empty cells
Relevancy 60.2%

First little back ground that what I am trying to do here We are using excel sheet to analyze our Lumber purchases Now I want to develop Access database to enter all this information I would like to use arrow down function to select the size and species of the lumber but the problem is that if I do I will end up with one column with all the species and sizes of the lumber as compare to right now they have different columns in Excel My problem These numbers are used to calculate some more stuff and they need to be copied over to different columns when analysis is done For example After interfacing my access database it will look like this Lumber species Qty Amount RO RO RO RO Ash Ash Now I want to create separate columns for each species and transfer qty and amount to these columns The condition is that amount has to be transferred on each ROW not total for the month data Excel the formulas analyzing for Any help will Excel formulas for analyzing the data be appreciated nbsp

Relevancy 60.2%

Hi guys,

I have an excel 2000 spreadsheet, that is used month after month. It contains various formulas throughout. I was told that there is a way to clear out all of the old data, but not the formulas. What we are trying to do, make a copy of the August spreadsheet, rename it to September, clear out all of the info, and start over, but maintain the formulas?

Does that make sense? Can anyone help me out with this?

Thanks a lot,


A:Clearing out Excel data but not formulas

1. Press F5 to display the Go To Dialog box

2. Click on the Special button and select Constants from the list and press OK

3. All cells containing numbers will be selected. Cells containing formulas will not be selected

4. Press Delete

If you want to include the command as part of a macro you can use the code.

Relevancy 60.2%

I have been trying to sort this out for days and each time formulas with trouble validation data I think I ve trouble with data validation formulas cracked it I hit on another problem I am trying to produce a rota for all the members on our team at work which cuts out the need for annotating printed copies by hand with everything self-generating within the spreadsheet So far I have the staff names in a list for the main part of the rota where you select from a dropdown That works perfectly The problem is the other part of the rota I can select from a dropdown to indicate when a staff member has annual leave or a training day but it is the days off in lieu of weekends that are causing me all the grief I am trying to reach a point where when you select a staff member with the day off option e g Joe Bloggs DO the dependent dropdown after it has the correct weekend days combined with the staff member s name the weekend preceding the Monday of that week s rota and the weekend following the Friday of that week s rota i e for week beginning Monday th December the dropdown would say quot Joe Bloggs DO four times respectively followed by the dates and Each week of the rota has the Monday date in the upper left cell with a relative row reference which adds seven each time you copy and paste in the same relative position The dates for that week simply add to the cell to their left so with each copy of the rota the dates effectively prefill automatically That works fine So far I have got a worksheet which has all the weekend dates listed against the date for each Monday of the weekly rota which has provided the source data for some of what I have tried to do This then feeds into a grid on the right hand side of the rota spreadsheet with a formula which concatenates the result of an index match function with each staff member s name I would like this grid to be dynamic but that is a separate issue for now I think I know how to do that having read stuff on contextures I have then tried creating a named range for each staff member which would be the source for the second dependent dropdown following Joe Bloggs DO and then used index and match to identify the row determined by the Monday date and the column determined by the staff member s name but as each Monday date has four dates associated with it I cannot get the row function to do what I want despite following instructions I have found on line for creating an index formula which returns multiple values for a unique lookup value Any ideas nbsp

A:trouble with data validation formulas

I'm getting closer to a solution - to make it easier, I have named the header row with the staff member's names, and named the entire area where the concatenations of staff name/dates are stored. This will eventually be dynamic.

So that each of the four weekend dates for each Monday are unique, I have added a column to the left which contains the column numbers referencing the table on the other sheet with the weekend dates in it. This makes it much easier both for copying and enables me to concatenate the column number with the Monday date so that each row has a unique reference.

The problem I am left with is how to get the INDEX/MATCH to work to pick out the relevant dates for each week of the rota. I can get it to pick out one but I have four rows with dates in and only one with the staff member's name so it would throw out the relative referencing (I think)
Relevancy 59.77%

Hi I want to copy the contents of cell A1 in to a separate worksheet based on cell A2 having a value in it. that bit is simple enough using the below formula but I then want it to not look at the blanks when copying into the new sheet so that the data is in one complete list without spaces. Useing the formula as below does the copying but but also copies the blanks and creates gaps on the new sheet which is what I want to avoid=IF(ISTEXT('Training by Role'!C5),'Training by Role'!B5,"")any help gratefully receivedthanks message edited by Peeteee

A:How to ignore blanks when copying data using formulas

Even though I am not clear on the desired output (based on your example data) perhaps this will work for you. We're going to use a "Helper Column"Start with the data you posted in Response #4.Use the formula you posted in your OP to create the extracted list - with the blanks - in another sheet. This is your "Helper Column". You can put the list anywhere you want.Next, name the column where your placed the list, using the Name "BlanksRange". Set the scope of the Name to the sheet where the list exists, not to the workbook. If you set the scope of the Named Range to a single worksheet, then you can use the same Name (and therefore the same formula) in each sheet. In other words, there is no need to use a different name in each sheet, you just have to create the named Range in each sheet and set it's scope to just that sheet.Now you can hide the Helper Column (the Named Range) if you want.Finally, use Chip Pearson's array formula on the Helper Column (the Named Range) to create a list without the blank cells. Do this in each sheet.=IFERROR(INDEX(BlanksRange,SMALL((IF(LEN(BlanksRange),ROW(INDIRECT("1:"&ROWS(BlanksRange))))),ROW(A1)),1),"")Because the name "BlanksRange" will only refer to the Named Range in the sheet in which you created the "worksheet scope" Named Range, each sheet will display the correct list based on that sheet's Helper Column.Keep in mind that an Array Formula must be entered with Ctrl-Shift-Enter for it to work. You can Ctrl-Shift-Enter the formula in the first cell and then drag it down as far as you need. Excel will know to create an array formula in each cell as you drag.I start with this in Sheet1
1 Safety Op Mgr
2 Abrasive Wheels
3 Additive Bags Disposal x
5 Asbestos Awareness x
6 ATEX Awareness x
7 Behavioural Safety Training x
8 BOAS - Cat 2 Steam Boilers
9 BOAS Boiler Operator x
10 Confined Space Entry x
11 Permit to Work x
In Sheet2, your ISTEXT formula creates this list. Column A is NamedBlanksRange using the worksheet scope.
1 Safety
3 Additive Bags Disposal
5 Asbestos Awareness
6 ATEX Awareness
7 Behavioural Safety Training
9 BOAS Boiler Operator
10 Confined Space Entry
11 Permit to Work
Finally, using Chip Pearson's formula on that Named Range, I get:
1 Safety
2 Additive Bags Disposal
3 Asbestos Awareness
4 ATEX Awareness
5 Behavioural Safety Training
6 BOAS Boiler Operator
7 Confined Space Entry
8 Permit to Work
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.message edited by DerbyDad03
Relevancy 58.05%

Alright so I have 2 workbooks lets say "workbooka" and "workbookb" they both contain the same column header "asset #". Workbooka has a list with no duplicates and is listed as column D. While Workbookb has a couple duplicates and is in column F. I wanted to be able to pull the information from workbookb into workbooka for only the "asset #" that match. Because of the duplicates however I have been running into a problem. Looking for input that does not involve a macros or add-in.

Thank you
Relevancy 58.05%

Hi,i found this question and seems near to my problem, but I totally dont know how to revise the code."Excel: Moving Column Data Into New Rows??"I have this data:UniqueID Company 1FullName 1Designation 1CountryCode 1Phone 1Email 2FullName 2Designation 2CountryCode 2Phone 2EmailR123456 Company1 Chris Reeves +65 90221111 [email protected] R234567 Company2 Sam Lim Sole Proprietor +65 9851234 [email protected] Samboy Lim Account +65 94890987 [email protected] Company3 Jose Accounts Department +65 67672345 [email protected] and I need the above data to be like this:UniqueID Company 1FullName 1Designation 1CountryCode 1Phone 1EmailR123456 Company1 Chris Reeves 65 90221111 [email protected] Company2 Sam Lim Sole Proprietor 65 9851234 [email protected] Company2 Samboy Lim Account 65 94890987 [email protected] Company3 Jose Accounts Department 65 67672345 [email protected] help.Thanksedited by moderator: Mask email addresses

A:Moving columns data into new row

I have edited your post to mask anything that looks like an valid email address. We do not want this forum to become a place where email addresses can be harvested. Once the bad guys start hanging around it's really hard to get rid of them.I can not determine the layout of your input or output data.Please click on the following line and read the instructions on how to post example data in this forum.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
Relevancy 58.05%


I am in need of a Excel tool (may be VBA/Fucntion..) where in I get the data from a spread sheet into another based on the 3 cells of a row.
EX: Excel 1 is having 3 columns defined (A, B, C) & each row in these columns will have predefined numbers as shown below:
Using these I need to get the data from Excel 2 (where we have the columsn A, B, C) for columns D, E....
Compare row 1: X1,Y1, Z1 in excel 1 & excel 2 to get cells D1, E1& F1 data to be filled from Excel 2.

Relevancy 58.05%

Hello Again

This is an Excel question. I have many rows in an Excel spread sheet that have duplicate data. These duplicate rows vary in content and in length each and every day. So far I have been deleting the duplicate rows manually. This task is now becoming time consuning. To make it easier on myself, I do a sort so that duplicates are all clumped together. For example I may the following in Columns A and B. Note, there are no spaces between different products.

000-00-001 product A
000-00-001 product A
000-00-002 product B
000-00-002 product B
etc and so on

I tried writing a macro to eliminate the duplicates only but it does not work properly. Can you help me on this one?


Relevancy 58.05%

In my quest to develop macros to manipulate our data I am trying to find a way to delete all rows that do not contain data in column J. Any ideas of how I could select rows without having the specific worksheet in the macro?

Whenever I try recording a macro using the sort function, it specifies the one worksheet where I recorded it.
This is what it recorded:
ActiveWorkbook.Worksheets("Z011_15A").Sort.SortFields.Add Key:=ActiveCell. _
Offset(-98, 0).Range("A1:A159"), SortOn:=xlSortOnValues, Order:=xlDescending _
, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Z011_15A").Sort
.SetRange ActiveCell.Offset(-99, -9).Range("A1:X160")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
End With

Relevancy 58.05%

Hi - i am trying to write a macro to move data from some rows to columns, but its not a straightforward transpose. Attached is the example

and if there are any blanks not to transpose those.


A:Macro column data into rows

Select your range of data to evaluate (in your attached example you would select C3:N12) and then run the macro below. The macro will create a new sheet with the transposed data.
Sub Transpose()

oldSheet = ActiveSheet.Name

Worksheets.Add().Name = "NewSheet"


For Each vcell In Selection.Cells

If vcell.Value <> "" Then

vRow = Sheets("NewSheet").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row

Sheets("NewSheet").Range("A" & vRow).Value = Range("A" & vcell.Row).Value
Sheets("NewSheet").Range("B" & vRow).Value = Range("B" & vcell.Row).Value
Sheets("NewSheet").Range("C" & vRow).Value = Cells(2, vcell.Column).Value
Sheets("NewSheet").Range("D" & vRow).Value = vcell

End If

Next vcell

End Sub
Relevancy 58.05%

Not sure where to post this; hope I found the right forum.

I have a set of texts in the source language in Excel. I have translated these into the target language. What I now want to do is match the source language data with my translations.

Is there any way to tell Excel that "when the text XXX is in column G, row 341, the text XXX is to be inserted into column H, row 341)?

Just an example.

In other words, what I want to do is make sure my translations are placed in the column to the right of each source language text.


A:Excel, matching data in rows

i have moved to business applications

in column H you can use IF

in H341
=IF(G341="XXX", G341, "what to do if not")

this will copy G341 into H341 only if G341 contains "XXX"

=IF(G341="XXX", "YYY", "what to do if not")

if what you want inserted is different text
Relevancy 57.62%

ive never done formulas in excel before except for basic sum stuff im trying to make a wage sheet which calculates hours worked and pay i have a row of cells under the titles monday to sunday with digits underneath saying how many hours were worked on those days however in the cells which say the hours worked each day i would also like to include overtime hours as a seperate digit but still remain in the same cell under the correct day e g under monday i might write being the normal hours worked and from 2003: single of use seperate which Excel 2 bits cell Formulas data a the value in Excel 2003: Formulas which use 2 seperate bits of data from a single cell the brackets being the overtime however id like these numbers to be calculated seperately as i have a column which gives a total normal hours worked and another column which says how many overtime hours were worked so basically in one column id like a formula which would work like sum first number in cell a first number in cell a etc and in another column id like a formula which would work like sum second number in cell a second number in cell a etc how can i do this ps im sorry if this has being asked before but i dont know how to word my search query cuz i dont know what this is called nbsp

A:Excel 2003: Formulas which use 2 seperate bits of data from a single cell
Relevancy 57.62%

Here is a sample of the table that I am working with:

Year Warehouse Item# Period1 Period2 Period3 Period4 etc....

There are many more columns but my issue is that if there is a 0 or blanks for all columns from period 1 to period 12 then don't show that row. How do I get this to happen with a query?

A:Access - hide row if many columns contain same data

In the first Criteria row for each period column enter
Relevancy 57.62%

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.

Relevancy 57.62%

I ve been at this for what seems forever so any help would be greatly appreciated I want to compare data from one sheet to another and for any exceptions non matches I want it to copy and paste the entire row into a rd sheet I ve searched and found various codes about comparing columns but my need seems to be a little more columns VBA Compare data Solved: complex I want it to compare columns on sheet columns A amp B against columns on sheet columns C amp D and it does not what row they are in I also would like the sheet name to be included in the results I ve Solved: VBA Compare data columns attached a sample book with the data Sheet shows what data should generate from this code These would be the results because the names both first and last Solved: VBA Compare data columns do not appear on both sheets I dont have a lot of experience with code so any help would be great Thank you nbsp

Relevancy 57.62%

Is it possible to fetch some lines of data from a group of web pages and paste in to Excel?Pls view the image for required excel columns.

urls are,
Stock Share Price dlf ltd | Get Quote dlf | BSE
Stock Share Price dlf ltd | Get Quote dlf | BSE
Stock Share Price jaiprakash associates ltd | Get Quote jaipra | BSE
up to 100 urls in a text file.

A:Web site data to excel columns

When you right click on the webpage and select "View source" from the context menu - do you see the info of interest; I don't. If the info were there, you could use InetGet. They know the value of the info being presented and they probably want to discourage automated data mining.

See if there is a mobile version of the webpage.
Relevancy 57.62%

Hello I have a really big problem in excel that I don t know how to do it In my workbook I have imported columns from txt files They are located in Z AA AB and AC Z amp AA contain the first and last name This can t contain any numbers AB amp AC contain the arrival departure hours This can data search same get two and columns VBA time t contain any words The list of names is made as the folowing the unique names always start from the top the dupplicated names always start after the unique list There can t be more than two instances of the same name and the list is contiguous Based on that I need to find only the dupplicated names that have one arrival and one departure hour in cells AB and AC So the first matched name should have AC blank and the dupplicate will have AB blank I will concatenate the names if it s easier to do the macro but I d rather not because the workbook itself is packed with loads of formulas and it s really making it difficult to calculate An example has been attached containg the VBA search two columns same time and get data expected result The workbook has sheets and they all use the same reference Z AA AB and AC from row and down Thank you for reading and for your intent to help I appreaciated Alex nbsp

Relevancy 57.62%

Hello I m hoping someone can lead me in the right direction here I have a sql query that was written data Extract SQL to specific columns for me some time ago I need to have the data placed in specific columns and rows within the csv file Is it possible for me Extract SQL data to specific columns to do this Here is what I have below Lets say I need to place quot ASSRREAL Street quot in column D row of the csv file Any help or ideas would be great Thanks SET NOCOUNT ON SELECT quot RTRIM ASSRREAL Unique id quot AS Uniqueid quot RTRIM ASSRREAL Taxpayer quot AS Name quot quot As Name quot RTRIM ASSRREAL In Care Of quot AS Careof quot RTRIM ASSRREAL Street quot AS Address quot RTRIM ASSRREAL Street Mailing Addr quot AS Address quot RTRIM ASSRREAL City quot AS City quot RTRIM ASSRREAL State quot AS State quot RTRIM ASSRREAL Zip ASSRREAL Zip quot AS Zipcode ASSRREAL Acreage AS Acres quot RTRIM ASSRREAL Map Block Lot Xtr Mbl quot AS MBL quot CONVERT varchar ASSRREAL Volume quot AS Volume quot CONVERT varchar ASSRREAL Page quot AS Page quot RTRIM LTRIM ASSRREAL Prop Loc St No quot AS Street Number quot RTRIM ASSRREAL Prop Loc St Name quot AS Street Name quot RTRIM ASSRREAL Prop Loc Unit quot AS Street Unit FROM ASSRREAL WHERE ASSRREAL Delete Flag N AND ASSRREAL GSequence AND ASSRREAL Record Year ORDER BY ASSRREAL Prop Loc St Name ASSRREAL Prop Loc St No ASSRREAL Prop Loc Unit nbsp

Relevancy 57.19%

@DerbyDad03 Your formula is great, except for 1 thing. I have 3 sheets, I move the information from 1st sheet to 2nd no problem and 2nd the 3rd. But wanted to make it so the information could be moved from the 2nd back to the 1st if needed or to the 3rd. When I add an additional IF statement (on the 2nd sheet) my macro doesn't run correctly.Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 7 Then If Target = "Demand Out" Then Application.EnableEvents = False nxtRow = Sheets("Demands").Range("G" & Rows.Count).End(xlUp).Row + 1 Target.EntireRow.Copy _ Destination:=Sheets("Demands").Range("A" & nxtRow) Target.EntireRow.Delete End If End If Application.EnableEvents = TrueEnd Sub
Relevancy 57.19%

This has been posted before and I attempted the macro given, but it did not seem to work. It only seemed to copy the data from one sheet to a "new".

Column A Column B Column C Column D
George 12345 Red Book
George 23456 Blue Television
George 34567 Orange Computer

Need data to appear as follows:
Column A Column B Column C Column D Column E Column F Column G Column H etc
George 12345 Red Book 23456 Blue 34567 Television

In my actual sheet, there 6 columns of data excluding the name (Column A). The rows are multiple.

Can someone possible help?

Relevancy 57.19%

I have 24 hrs of data in seconds across 49 columns. I need to average the seconds to minutes and then hide the seconds. It would be preferable to remove the seconds once the minutes are averaged if possible

A:sort, average and hide rows of data

If you would rather do the Averaging on a second sheet, you can.Your sheets should be named Sheet1 & Sheet2Sheet1, all your data across 49 Columns and should look like:
1) Time Label_1 Label_2 Label_3 Label_4 Label_5 <> Label_47 Label_48 Label_49
2) 0 21.384 21.081 21.716 21.558 21.855 21.716 21.558 21.855
3) 1 21.372 21.056 21.722 21.545 21.83 21.722 21.545 21.83
4) 2 21.374 21.048 21.702 21.55 21.864 21.702 21.55 21.864
5) 3 21.359 21.062 21.706 21.523 21.84 21.706 21.523 21.84
6) 4 21.371 21.052 21.693 21.561 21.861 21.693 21.561 21.861
7) 5 21.359 21.043 21.706 21.533 21.852 21.706 21.533 21.852
8) 6 21.374 21.056 21.716 21.548 21.855 21.716 21.548 21.855
9) 7 21.368 21.042 21.718 21.544 21.851 21.718 21.544 21.851
10) 8 21.371 21.046 21.709 21.544 21.851 21.709 21.544 21.851
Sheet2 will look like:
1) Sheet1 Label_1 Label_2 Label_3 Label_4 <> Label_47 Label_48 Label_49
2) 2 61
3) 62 121
4) 122 181
5) 182 241
6) 242 301
7) 302 361
8) 362 421
9) 422 481
10) 482 541
The word Sheet1, must go in cell A1Put this new formula in Sheet2 Cell C2:=AVERAGE(INDIRECT($A$1&"!"&SUBSTITUTE(ADDRESS(1,COLUMN()-1,4),"1","")&$A2&":"&SUBSTITUTE(ADDRESS(1,COLUMN()-1,4),"1","")&$B2))It is a bit long, so you might want to just Copy & Paste.Then drag across to Column Ax then drag it down.See how that works for you.MIKE
Relevancy 57.19%

Hi All,

got a quick question wherein Row 3 to Row 30 are hidden in Sheet2 and is reading the data from "Sheet1", need hidden rows to unhide only when input the data in Column C and D in Sheet1.

And, merge the cells with same "Team Name" after sorting if not in order in Sheet2 (sample sheet attached & available at

Relevancy 57.19%

I'm new to macros. Is there anyone who can sort information in an infinite number of columns and an infinite number of rows by information contained in each row? Some rows will contain only one column and others will have several columns.
Additionally, after the sort, I would like to total the number of like rows and insert that total in column "A" in the front of the rows, deleting all but one of the like rows. Also, there is data in parentheses that I would like to add together, per row and insert the sum in column "B" at the front of the row.
I have attached before and after files.
Thanks for any help. I have spent a lot of time trying to figure this out, but I'm not savvy enough to do it. And if I didn't explain this well enough, I'll try to clarify. Westcap

Relevancy 57.19%

Hi I have a workbook sample attached which Copy data Solved: inserted rows to lists customer data in cols A to J Each customer has a main ref col B and may have any number of secondary refs in cols L onwards Col K shows Solved: Copy data to inserted rows how many secondary refs the customer has and is used in the code below to determine how many blank rows to add for those customers that have or more secondary refs row per ref Sub Insert Row Dim LASTROW As Long Dim I As Long LASTROW Range quot C quot amp Rows Count End xlUp Row For I LASTROW To Step - If IsNumeric Cells I quot K quot And Cells I quot K quot lt gt Empty Then Range Cells I Cells I Cells I quot K quot EntireRow Insert End If Next I End Sub The attached shows the worksheet having run the code What I need now is for the code to do more things Once the blank rows have been inserted for e g the customer in row the value in L to be copied to B and the value in M to be copied to B Could select then transpose Then copy the values in cells C J into the same columns in rows amp Not bothering with col A Sheet shows the end result I m after Basically I m consolidating main and secondary refs for each customer into one list Hope someone can help Cheers Phil nbsp

Relevancy 56.76%

Hi all,

I am using the below formula (that I found on this forum) to calculate the age based on a DOB. I now want to create a formula for the next column to have either ‘Yes’ or ‘No’ if the age is above a certain value.

If this is possible, is it then possible if I have two ages in separate columns to generate a ‘Yes’ if either of the age’s are above the specified Value?

Formula for Age Calculation;
=DATEDIF($C24,NOW(),"y") & "y,"&DATEDIF($C24,NOW(),"ym") & "m,"& DATEDIF($C24,NOW(),"md") & "d"

Any help would be greatly appreciated,

Many thanks.

A:Data in columns formula to generate a new column

try this

=IF (Cell you do the calculation in > age you want to test , "Yes" , "No" )
if you want to test two columns , then

=IF ( OR(column1 > age, column2 > age ), "Yes", "No")

can you load a dummy spreadsheet and we can check the above works OK
Relevancy 56.76%

I m hoping there 2 3 columns compare EXCEL files data in is a formula within EXCEL that will allow me to complete this comparison I ve been working on it all day manually and realize it will take a long time to complete this way My computer is running on Windows Home Premium and my EXCEL is I have two different files and within each are three columns of data that I need to compare They re labeled NC NCI and SECNCI To be considered a match I need all three fields within a row to match exactly to a row within the other file The first file called REQUEST NET FILE has over rows of data so naturally there will be many repeats of these same three fields in that file but each row where the three fields match any row of data on the second file called DEPLOYED TABLE FILE needs to be the same to be considered a match If it doesn t match if possible I d also like to know why This could fall into two different scenarios NC CODE IS NOT ON DEPLOYED TABLE FILE or NCI SECNCI combo not on deployed table for this NC Once complete I ll run the same process in reverse so both files will end up with a match or no match next to each row If I need to run two separate compares one to EXCEL 2 files compare data in 3 columns get the match and then one on just the no match results to find the second bit of info I can do that Or if the second criteria can t be found via excel formula just the match no match result will be acceptable as that will save me so much time I was thinking a vlookup could do the compare for me but I m not good with vlookup formulas and after struggling a few hours with it I started searching and came across your site I ve found a few similar topics but nothing that I can translate into something for my specific need I ve attached portions of the two files and you can see on the REQUEST NET FILE where I ve managed to get to with doing it manually It s quite a tedious process so I m really hoping you can help Thank you Tech Support Guy System Info Utility version OS Version Microsoft Windows Home Premium Service Pack bit Processor Intel R Celeron R CPU GHz Intel Family Model Stepping Processor Count RAM Mb Graphics Card Mobile Intel R Series Express Chipset Family Mb Hard Drives C Total - MB Free - MB Motherboard Dell Inc G F Antivirus McAfee Anti-Virus and Anti-Spyware Updated and Enabled nbsp

Relevancy 56.76%

Hi everyone

I'm a newbie here, so hoping someone can help me!!
I have Names and Mobile Number in Column A. I need to find a way of moving Mobile Numbers into Column B - please tell me there is a simple way in which this can be done.....HELP!

Relevancy 56.76%

I have data organised in paris of columns with differing amounts of data in each column pair.
I need to move all data to columns A&B:

- If there is data in columns C & D, move it to the foot of columns A&B.
- Then, if there is data in columns E & F, move it to the foot of columns A&B.
- Etc. til the end of the ActiveRange.

I've managed to do this using hard-coded column references, but it involves a lot of reptetition.
Presumably the code can be substantially compacted, and work to the end of the ActiveRange rather than to arbitrary end columns (in this case, cols AC and AD).

A:Solved: Excel VBA moving all data to columns A+B

This will work on the active sheet.... If you need this for multiple sheets we would need to alter the macro slightly. Test this on a copy of your xl file.


Sub appAB()

Dim i As Integer
Dim lRow As Integer
Dim lRowA As Integer

For i = 3 To 30 Step 2
lRow = Cells(Rows.Count, i).End(xlUp).Row
lRowA = Cells(Rows.Count, 1).End(xlUp).Row + 1
Range(Cells(1, i), Cells(lRow, i + 1)).Cut Destination:=Range("A" & lRowA)

End Sub
Relevancy 56.76%

I am having two column with current month profit & last month profit respectively Column A & Column B. I would like to compare both column & set the ICON (down Arrow & up Arrow ) based on result if A1 value greater than B1 set upper arrow else down arrow

A:How to set ICON in Excel by comparing data in 2 Columns

You didn't say where you wanted the arrows to appear, so I'll use C1 for this explanation.1 - In C1, enter this formula: =IF(A1>B1,2,IF(A1=B1,1,0))2 - Under the Conditional Formatting drop down, choose Icon Sets and then choose the Up arrow.3 - Go back into Conditional Formatting and choose Manage Rules4 - Edit the Rule for C1 as follows:----- Click the Show Icon Only box to place checkmark in box----- Change Type to be Number in both dropdowns----- Up Arrow: when value is >=2----- Sideways Arrow: when <2 and >0----- Down Arrow: when <=0 (should be default)5 - Click OKLet me know how that works for you.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
Relevancy 56.76%

On the attached spreadsheet, I need a total count of baselined desktops (minus the ones that have been deleted) problem is how do I get it to look at one record, mark that that agent had been a baselined one, but is now deleted, so don't count it. I can't just remove the deleted ones, cause I want my customer to know which agents have disappeared. Take a look at the formula in B2 for a general idea. That formula doesn't work cause it just adds everything together. I need it to be smart enough to Total the Baselined Agents and then Subtract the agents that are baselined and deleted per field. Any questions let me know. Look at B2 to get an idea and see if I'm on the right track.

A:Solved: Excel - Trying to count 2 columns (almost as one) to get data for a 3rd.
Relevancy 56.76%

Hi - I m semi-proficient with excel and I m actually working on creating some dashboard reports at this time One problem I m having is separating some of the data from a specific column Let me give you an example This column has over lines in Excel columns? separation Data Solved: this is just a sample What I need to do is break this column out by month and show how many dates are from the last month the last - months and the Solved: Data separation in Excel columns? ones that are months in the past Of course I want the sheet to show this info automatically so it has to know the current date which I can do by placing the formula quot TODAY Solved: Data separation in Excel columns? quot So what I m asking for is what formula and format would I use to count how many cells in this column fall into each of date ranges I mentioned Also I did manage to use the DAYS A C for example where A quot Today quot and C quot quot above This information is in cells next to the above data as follows - - - - - - - - - - - - - - - - So I figured out how to list how many days I have since the date Solved: Data separation in Excel columns? in the first column but not how to break them apart within the column Hopefully this is enough information to request some help but if you need more info please let me know I ll watch the thread closely Thank you -Drake nbsp

Relevancy 56.76%

I have a large excel file (3k= rows) of name/addr info. I want to transpose the date so information appears in its own column for sorting, etc. The problem is the data is not consistant. (sometimes there is two address enteries, sometimes three. In another record there may be two phone numbers. So each company may have six or seven rows of info. The only consistancy is that the company name is in bold. I've tried to attach a sample file so you can see what my task is. I'm not real good with Excel, so treat me like a dumy; it's OK.
I really appreciate the help.


A:Transposing column data into multiple columns

Bill, that was an awkward one, but I think this does what you want.
Just click on the "CommandButton1" and watch.
Relevancy 56.76%

I have an excel sheet with 3 columns. What I want to do is, I need to be able to find value in column 3, that corresponds to a value in column 2. The value in column2 should match the value in col 1.


Input Data
Col1 Col2 Col3

The result should be
Col1 Col2 Col3
Please help me.

Relevancy 56.76%

I have two columns of data. In column A, I need to identify every cell that is identical to any value in column B.

Relevancy 56.76%

Hi I have Worksheets I would like to compare the columns and add missing employees to Different Worksheets Columns Add Data on and Missing Compare the main worksheet and highlight any employees that no longer on the nd sheet Sample - Sheet Plant - PA's Column A Compare Columns on Different Worksheets and Add Missing Data Column B Column C Row Employee Name Employee ID Start Date Row Employee VLOOKUP Formula to Sheet VLOOKUP Formula to Sheet Row Employee Compare Columns on Different Worksheets and Add Missing Data VLOOKUP Formula to Sheet VLOOKUP Formula to Sheet Row Employee VLOOKUP Formula to Sheet VLOOKUP Formula to Sheet Row Employee VLOOKUP Formula to Sheet VLOOKUP Formula to Sheet Sample - Sheet Employee List Query Column Compare Columns on Different Worksheets and Add Missing Data A Column B Column C Row Employee Name Employee ID Start Date Row Employee Row Employee Row Employee Row Employee In the above example I would like to be able to add Employee from the Employee List Query to the bottom of the Plant - PA's sheet along with the VLOOKUP formulas in Columns B C I would also like to highlight Employee on the Plant - PA's as it has been removed form the Employee List Query sheet Hopefully this makes sense and is possible Thank you Sandi

A:Compare Columns on Different Worksheets and Add Missing Data

Hi,Here is an example of how i would do it, usually others will have a much cleaner and probably better way of coding the solution, however this is my way of doing it.Hopefully DerbyDad can provide a solution too.There are two method which you will need to call1) CopyMissingEmployeeThis will call IsOnOtherList and check if the employee exists on the other list, if not then the entire row is copied to sheet 12) HiglightRemovedThis will call IsMissing and will highlight any employees on sheet 1 where they do not appear on sheet 2.Hope this helpsPrivate Sub CopyMissingEmployee()

Dim URange, LRange
Dim BCell As Range
Dim NextRow

Set URange = Sheet2.Range("A2")
Set LRange = Sheet2.Range("A" & Rows.Count).End(xlUp)

For Each BCell In Sheet2.Range(URange, LRange)

If IsOnOtherList(BCell.Value) = False Then


NextRow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row

Sheet1.Paste Sheet1.Range("A" & NextRow + 1)

End If

Next BCell

End Sub

Private Function IsOnOtherList(EmpID As String) As Boolean

Dim URange, LRange
Dim BCell As Range

Set URange = Sheet1.Range("A2")
Set LRange = Sheet1.Range("A" & Rows.Count).End(xlUp)

For Each BCell In Sheet1.Range(URange, LRange)

If BCell.Value = EmpID Then
IsOnOtherList = True
Exit For
IsOnOtherList = False
End If

Next BCell

End Function

Private Sub HiglightRemoved()

Dim URange, LRange
Dim BCell As Range
Dim NextRow

Set URange = Sheet1.Range("A2")
Set LRange = Sheet1.Range("A" & Rows.Count).End(xlUp)

For Each BCell In Sheet1.Range(URange, LRange)

If IsMissing(BCell.Value) = True Then

'BCell.EntireColumn.Interior.Color = vbYellow
BCell.EntireRow.Interior.Color = vbYellow

End If

Next BCell

End Sub

Private Function IsMissing(EmpID As String) As Boolean

Dim URange, LRange
Dim BCell As Range

Set URange = Sheet2.Range("A2")
Set LRange = Sheet2.Range("A" & Rows.Count).End(xlUp)

For Each BCell In Sheet2.Range(URange, LRange)

If BCell.Value <> EmpID Then
IsMissing = True
IsMissing = False
Exit For
End If

Next BCell

End Function
Relevancy 56.76%


First, thank you all for you time and knowledge in advance.

Here's my situation. I'm working with file that is sent to me with populated information, in this example Col A
What I would like to do is enter information in Col B and then do a comparison of both A and B, letting me know if something in Col B is not in Col A and also is something in Col A not in Col B.

I need to know if what was sent to my in the spread sheet match what I physically inventoried.

I have provided an example.

Hopefully I did not over complicate my request.

Thank you,


A:Solved: comparing data in two columns in Excel

does this help

or this may ble closer to what you need
Relevancy 56.76%

I have a column that contains the full name of a client, eg. Mr B Cooney and I want to divide this into three columns (Title, Firstname and Surname). Can anyone help?

Relevancy 56.76%

Hi I m trying to get a Word doc a meeting agenda to populate using data from an Excel sheet and have run into a couple of challenges I have Word and Excel and am currently using labels and fairly basic coding via Developer in Word I feel like there is likely a way VB) to multiple rows Excel data (using from of Pulling Word to do what I m trying to do but unfortunately haven t been able to figure it out - and Pulling multiple rows of data from Excel to Word (using VB) haven t had much luck online So I m hoping you might be able to help I m including a description of the challenges I ve run into below along with an image of the spreadsheet and the Pulling multiple rows of data from Excel to Word (using VB) coding I m currently using Any help at all would be really appreciated If there is any other info I can provide that would be helpful please let me know Thanks so much L Challenge Is there a way to make the data area in Word automatically grow to accommodate the text from Excel One of the items I m including is comments which could range from three words to lines It seems like the only two options with a label is for the data to be partially cut off using wrap or for it to resize itself I d like to have the field in Word automatically extend if needed to fit all the text Is this possible Challenge a While I have coding that works to draw the data in I m hoping that there is a way to do it without having to manually update the coding for all line items in the Word doc Right now the Excel Spreadsheet has about rows and columns - it is generated by a database that doesn t provide options other than quot run report quot Basically I need data to pull from each row into a Word doc with - rows per paragraph The spreadsheet looks like this this is not the real data With the current coding below for each paragraph in the doc I m having to go through and change the info below to refer to the correct label ex update Country to Country update cells to etc for each agenda item which has become really time consuming Is there a way to write the coding so that it automatically populates The excel spreadsheet looks something like this This is how I have the labels set up in the word doc Country Last Date A RevdDate Item Status Comments Item Status Comments and then once the command is run it looks like USA James Original Date Revd Date Tech Eval Complete Comments evals completed for all tech areas - data submitted amp reviewed by JM - player notified meeting held and player attending appropriate sessions Tact Eval Complete Comments evals completed for all tactical components - data incl images submitted through tracking program - rev d by KO - player notified meeting held process complete This is the coding I m currently using that would populate the first two items quot players quot in the agenda Private Sub CommandButton Click With Comments Automatically size the label control AutoSize False WordWrap True Set the font used by the label control Font Name quot Calibri body quot Font Size Font Bold False End With With Comments Automatically size the label control AutoSize False WordWrap True Set the font used by the Label control Font Name quot Calibri body quot Font Size Font Bold False End With Dim objExcel As New Excel Application Dim exWb As Excel Workbook Set exWb objExcel Workbooks Open quot c users me my documents ops Reporting xlsx quot ThisDocument Country Caption exWb Sheets quot Reporting quot Cells ThisDocument Last Caption exWb Sheets quot Reporting quot Cells ThisDocument DateA Caption quot Original Date quot amp exWb Sheets quot Reporting quot Cells amp quot quot ThisDocument RevdDate Caption quot Revd Date quot amp exWb Sheets quot Reporting quot Cells amp quot quot ThisDocument Item Caption exWb Sheets quot Reporting quot Cells amp quot quot ThisDocument Status Caption exWb Sheets quot Reporting quot Cells ThisDocument CommentDate Caption quot Comments quot amp exWb Sheets quot Reporting quot Cells amp quot quot ThisDocument Co... Read more