Hi all,

I have an excel sheet attached as an example. What I want to do is

take the data in each column and put them together into one

column.

In my attached excel sheet I would like to have the data in each

column look like this in one separate column each line for example 1-72-bk-1803

any help is appreciated....thanks

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 __ http://downloadreimage.com/directdownload.php__. (This link will automatically start a download of Reimage that you can save to your computer.)

In E1 enter

=A1&"-"&b1&"-"&c1&"-"&d1

copy down

Hi,

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.

Example

Input Data

Col1 Col2 Col3

1------2------A

2------7------B

7------1------C

5------9------D

The result should be

Col1 Col2 Col3

1------1------C

2------2------A

7------7------B

5------9------D

Please help me.

https://forums.techguy.org/threads/excel-compare-2-columns-and-get-data-from-3rd-column.853589/

Hi I amp m trying to work out how I can look at one column in a spreadsheet and display the text in another field but without a load of blank cells in the results column Let me give you an example On Sheet In column A are road names In column B the number of building on that to from multiple one data column 2010 Excel columns separating road In column C the last date and work was doneon the road In Column D is an IF formula thatbasically gives a result of Excel 2010 separating data from one column to multiple columns or On another sheet I want four lists Sheet In column A is a list of road names thathave a value of in Sheet column D In column B is a list of road names thathave a value of in Sheet column D In column C is a list of road names thathave a value of in Sheet column D In column D is a list of road names thathave a value of in Sheet column D I can do this with a simple IF statement in columns A B C amp D on sheet and drag it down but there are a couple of problems with this method If the value in sheet doesn amp t match I get a blank cell on sheet as there should be but this leaves a large number of cells with no value I need a list in each column A B C amp D onsheet without any blank cells I amp m going to be pasting different data into columns A B amp D on sheet a number of times and the number of rows in will be different each time I remember seeing something before thatinvolved using the IF and COUNTIF function in a formula but not sure how Any help would be appreciated nbsp

should be able to use an array formula - so use control + shift + enter key and the formula gets { } brackets

Code:

=INDEX(Sheet1!$A$2:$A$14,SMALL(IF(Sheet1!$D$2:$D$14[COLOR="Red"]=1[/COLOR],ROW(Sheet1!$A$2:$A$14)-ROW(Sheet1!$A$2)+1),ROWS(F$4:F4)))

where you change the red =1

to =2, 3, 4 for different columns

does that work out

see attached

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

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!

https://forums.techguy.org/threads/solved-data-from-one-column-to-split-into-two-columns-how.726727/

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.

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

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.

Bill

Bill, that was an awkward one, but I think this does what you want.

Just click on the "CommandButton1" and watch.

I have a database which has about 60000 rows and 3 columns.

Here I have made a picture to show what exactly I want, this picture represent a general view of my data structure and also represent what I want.

I have 3 first columns (from left) and I want to create a new column like the last one which named New Text.

As you see I want to create a column which duplicate the Text of every row for X times. And X is the frequency of repeated number in Number ID 2.

For example: In Number ID 2, 8 repeated four times. So New Text should have four H. (H represent the text)

Please consider this to be done by Microsoft Access software, which I have no experience of working with.

will take TWO updates.

1) a transaction to ADD Column "New Text"; commit

2) another to UPDATE "New Text" where "Number ID 2" equals "Number ID 1" value is TEXT;

(or something close to that - - it's be a while since I last used SQL grammar.

I'm using some software to export payment information into a CSV file. This file needs to be in the bank's specified SIF format which requires all the data to be in one column.

The limitation with my software is that the detail of each payment gets exported into it's own separate column. For instance, cells A1:A10 contain payment details to one individual, B1:B10 to another, and so on. I require the whole lot combining into Column A, running from A1:A65536 (it will never go this far down the spreadsheet, but you get the idea).

I've tinkered with some basic copy and delete macros but I'm unable to make the headway that I need to. I don't have the touch when it comes to programming .

Thanks in advance

https://forums.techguy.org/threads/solved-excel-macro-to-combine-all-columns-into-column-a.939255/

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.

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.

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

Thanks for any help.

-wayne

https://forums.techguy.org/threads/is-it-possible-to-subtract-data-columns-in-excel.565741/

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).

Hi

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.

Code:

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)

Next

End Sub

Helloooo

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

https://forums.techguy.org/threads/excel-2000-filtering-columns-of-data.797387/

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

https://forums.techguy.org/threads/excel-2-files-compare-data-in-3-columns.1046092/

On the attached spreadsheet, I need a total count of baselined desktops (minus the ones that have been deleted)...my 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.

https://forums.techguy.org/threads/solved-excel-trying-to-count-2-columns-almost-as-one-to-get-data-for-a-3rd.737946/

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

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.

http://www.computing.net/answers/office/how-to-set-icon-in-excel-by-comparing-data-in-2-columns/20422.htmlHi 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

https://forums.techguy.org/threads/excel-searching-data-in-rows-columns.246570/

All,

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,

miketx

does this help

http://support.microsoft.com/kb/213367

http://office.microsoft.com/en-gb/excel-help/use-excel-to-compare-two-lists-of-data-HA001103915.aspx

or this may ble closer to what you need

http://forums.techguy.org/business-applications/174998-excel-compare-2-lists-highlight.html

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

https://forums.techguy.org/threads/solved-data-separation-in-excel-columns.860839/

Hey Guys,

I scanned in some phone numbers/names using OCR and need some help in Excel. Here is example I made real quick of what the list looks like:

Column A is fine. However, do you see Column B? It has the grade and then the phone number.

Here's what I mean in column B:

Grade [one space] phone number

So, it looks like this in the excel spreadsheet:

10 555-5555

Here's what I need to do. I need to find a way to automate the process of taking out the phone numbers from column B and moving them all to a new column (C). There are four grade numbers (9, 10, 11, and 12), so I can run the process on each grade to separate them.

How can I do this? Thanks.

Put the formula:

=RIGHT(B2,8)

in C2. Adjust the 8 for the phone number if it includes area code. Then drag that formula down the number of rows you'd like.

- Castleheart

What would be the best way to find a specific Column Header, and copy the data found in the column until the column header is found again?

https://forums.techguy.org/threads/solved-excel-macro-copy-data-from-columns.809137/

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

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

Hi This is a fab site just spent a good few hours reading through without realising the time ANyway I am stuck on an Excel problem I have two columns of which there are lots of duplicates in column A and various values in column B Like this A B Book Title Song One Book Title Song Two Book Title Song THree Book Title Song One Book TItle Song Two Book Title Song Three Book TItle Song Four and so on for rows Merge two Solved: a Data Columns from row Sort Excel and into What I would like to do is have all of the relevant info from column B in just one cell in one row rather than a separate row for each song title So what I ideally need is A B Book Title One Song One Song Two Song Three Book Title Two Song One Song Two Song Three Song Four and so Solved: Excel Sort and Merge Data from two Columns into a row on Solved: Excel Sort and Merge Data from two Columns into a row I have no idea where to begin with this I have read several similar Solved: Excel Sort and Merge Data from two Columns into a row things that might help but the problem is that each Book Title has a different number of songs in it Can any one advise a complete beginner on how I might accomplish this Many thanks for any guidance nbsp

https://forums.techguy.org/threads/solved-excel-sort-and-merge-data-from-two-columns-into-a-row.990053/

I would like to find duplicates in a column for fields that contain "_" only and place on new sheet in column A. If found, I would like to then get the data in the adjacent field only if it I not blank and put the first instance value of adjacent column in Column B on new sheet, second instance Column C, etc. Probably no more than three instances. I have an example spreadsheet that has sample data and a results tab of how I would like the valid data to look. I am not sure where to start on coding it and which formula would be the right ones to use. I don't necessarily want the exact code but some guidance on the steps to take and formulas to try.

https://forums.techguy.org/threads/solved-vba-excel-find-dup-in-column-and-get-adjacent-data.1145052/

If you check the file attached.

please can you help me in moving data with duplicate names to have the 2nd and 3rd email address moved to a new column and delete the row

need an excel macro to automate this as i am working with a large number of records like this in one excel sheet. i.e, i want to select the sheet and move data in every 2nd row to the columns in row above at the end of columns in that row.

I'm unable to understand the entire requirement, but for moving the records with duplicate names, I've written few lines. This will cut the duplicate record rows (Consider "Pupil Forename" for removing)from the "Report Data" and will post into "Sheet3". Before running this macro please insert "Sheet3". Let me know your requirement, hope I will be able to resolve it out. Thanks!

Code:

Sub test()

Sheets("Report Data").Select

Dim trow As Long

trow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

For i = trow To 1 Step -1

If i = 1 Then

Exit Sub

End If

If Cells(i, 1).Value = Cells(i - 1, 1) Then

Rows(i).Select

Selection.Cut Destination:=Sheets("Sheet3").Range("A65536").End(xlUp)(2)

End If

Next i

End Sub

I have a excel file like below.

Column1 |Column2|Column3|Column4|Column5|Column6|Column7

Row 1 EEEE 1 2 3

Row 2 4 5 6

I want to move data in row 2 (4,5,6) to Row 1 columns 5,6,7.

Could you help me with an excel macro to automate this as i am working with a large number of records like this in one excel sheet. i.e, i want to select the sheet and move data in every 2nd row to the columns in row above at the end of columns in that row.

your assistance is greatly appreciated.

thank you

https://forums.techguy.org/threads/excel-macro-to-move-data-in-rows-to-column.744565/

Okay - still very new to excel formulae so bear with me...

ColA.......Col B

Open..... 23

Closed... 7

Open......4

Open..... 8

Closed... 19

Okay let us assume in C1 I want a single formulae that wil:

a: count the rows that have 'Open' in column A

b: sum the corresponding values in column b

With this example, my visible value in C1 should be 35. I am hoping to not have to 'duplicate' data in another column/area and the sum that new column (such as copying only the 'B' values that have "Open" in the 'A' column and then running a sum off that new column....

I am sure there is an easy way to do this but I keep getting zeros. I have been trying this:

=SUM(IF(A2:A5000="*Open*",B2:B5000,0))...

I have one other formula in D1:

=COUNTIF(AF1:AF5000,"*Open*").. which displays 3....

Thanks in advance

Ric

=SUMIF(A2:A5000,"*Open*",B2:B5000)

Hi,

I was hoping somone could please help me out.

I have an excel spread sheet with a lot of data in columns which i need to convert into rows. The problem is one column contains over 10,000 rows of data... thus making it too hard to transpose manually.

I have attached a copy of the spreadsheet (condensed version) and was hoping someone had some suggestions on how i can achieve my desired outcome.

Thanks very much,

Ray

I'm sorry to say that this is pretty much impossible as far as I can tell because there simply isn't enough consistency in your layout. If all rows were there and in order it would be easy enough.

As they aren't, one would need to inspect the titles to put them in the right place, but your titles don't always match either. For example your second "Telephone" entry is actually titled "Clinic Mobile", which the computer justly sees as very different indeed.

I'll try to help if I can, but you'll need some point of consistency if you want computers to automate a process. If it helps, here's a copy of my spreadsheet that inspects the titles (on sheet 2). If there are only a few problematic titles it might be easier to correct them manually (or use search and replace).

Hi all I have a spreadsheet that holds sports results based on age groups The age groups are in one column stored as U B U G U B U G U B U G which is the age B or G boy girl The results are in another column stored as whole numbers I am trying to find a formula that will allow me to sort on the first column in the order shown above and then on the second column in descending order and then bring back the other data like the name example Child A U B Child B U G Child C U B Child D U G Child E U G would be returned as U B Child C U G Child D U G Child B U G Child E U B Child A I know I can use a manual sort but I was hoping to automatically put the results in a separate sheet in the correct order Any ideas Thanks in advance nbsp

You could easily enter in this formula:

=IF(LEN(B1)=3,LEFT(B1,1)&"0"&RIGHT(B1,2),B1)

(where the three data columns are just A, B, and C) so that you get - instead of U6B, for instance - U06B. This allows you to sort correctly by age.

If you want it copied and sorted to another page like that, you really need a macro/VBA.

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.

https://forums.techguy.org/threads/solved-excel-macro-to-move-data-in-rows-to-columns.1021038/

Hi, I am new here. I need help to write macro/code which can submit query in the application status feedbox at http://124.124.193.235/eregister/eregister.aspx

and click on SUBMIT. From the results, i want to copy STATUS data to excel sheet.

For example query numbers are 1274032, 1460986, 1522002 in the excel column.

For 1274032 the result page shows STATUS as REGISTERED. I want REGISTERED copied to adjacent cell of 1274032 cell

Please help me.

https://forums.techguy.org/threads/solved-how-to-pull-data-from-webpage-aspx-for-a-excel-column.1058880/

In Excel 2007, I have a spreadsheet with 14 columns. Twelve of the columns contain categories of data that correspond to the entries in the other two columns. I need to change the list so that each item/amount in each category occupies its own row, as follows:

Eg.

From this:

col1-----col2-----category1-----category2-----category3-----etc... up to 12 categories

aaa------bbb---------------------$55

ccc------ddd------$44--------------------------$66

To this:

col1------col2------category------amount

aaa-------bbb------2-----------------$55

ccc-------ddd------1-----------------$44

ccc-------ddd------3-----------------$66

I know how to do this manually, but it needs to be done frequently on many different sheets. So, I need a way to do it automatically.

Many thanks for your help

jeannie

I have your data mapping from A:N to P:S, with no headers. In case that's wrong, make sure your work's saved before trying this.

Sub test()

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

For i = 1 To LastRow

For Each Cell In Range("C" & i & ":N" & i)

If Cell <> "" Then

DestRow = WorksheetFunction.CountA(Columns(16)) + 1

Cells(DestRow, 16).Resize(, 2).Value = Cells(i, 1).Resize(, 2).Value

Cells(DestRow, 18) = Cell.Column - 2

Cells(DestRow, 19) = Cell.Value

End If

Next Cell

Next i

End Sub

HTH (welcome to the board)

Hi, I need a macro to use in Excel 2003 that will select all data in a column except the header row. At the moment i am using the following command which works well in most cases

Range("A1").Select

ActiveCell.Offset(1, 0).Select

Range(Selection, Selection.End(xlDown)).Select

The catch to this is that if the column contains only one row of or no data then the command selects the entire column! Eek, can anybody please help me to fix this?

Dim lastRow as long

LastRow = worksheetfuntion.Max(2, Range("A" & rows.count).end(Exlup).Row

The variable will contain the last filled row in column A, the Max function will make sure that it will at least be row 2 just in case the column is empty

Then all you need is Range("A2:A" & lastRow).Select

Or if you need more columns then Range("A2:H" & lastRow).select

I have an entire column of data that I need in one cell. When combined I need each piece of data to be surrounded by " " and a , between each one.

151

183

2001

results = "151","183","2001"

ASAP's "Merge row data" ("Columns and rows" category) will do 99% of the work for you.

http://www.asap-utilities.com/

(edit: "entire column" -- do you mean full from top to bottom? )

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

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

Regards,

Rollin

A newbie question. I haven't used Excel since 2004 and now I'm trying to reacquaint myself with the basic formulas and functions. How can I design a spreadsheet to make a formula carry over to a new line when a new row is entered. For example if I have C3 with the formula =Sum(A1+B1), and someone wants to add a A2 and B2, how can I make it so that C3 performs similarly to A3, as in how can I make the formula copy itself to every newly entered row without having to copy and paste the formula along the entire column. I want to use the If function, but copying the formula down the column displays the "false" value even if there are no entries. Thanks.

It might be pretty tough to get Excel to auto enter a formula in a row that has just been added.

However, you can test for a blank or null by using the IF and ISBLANK function.

Something like

=if(isblank(some condition), what to do if true, what to do if false)

For Excell 2000 -- In a column e.g A1 to A5 - how can I get cell A6 to return the last entry in the column? Logically the formula would check the cells starting w/ A5 for "Emptyness" and would keep going sequentialy until it got to a cell that had either a text or numerical entry.

cat

dog

3

Bingo

Bingo -- cell A6 would return last entry -- "Bingo"

or

cat

dog

3

3 -- cell A6 would return last entry or "3"

Column lengh can be any number of cells long but each cell is filled sequentialy - A1 then A2 then A3 etc

My only success (limted) is w/ nested "IF/THEN" statments and then I was limted to a column of 5 entries --

Any suggestions would be greatly appreciated!!!!

https://forums.techguy.org/threads/excel-question-last-entry-in-column.324036/

Hi

I'm doing a list of words and their meanings in MS Excel 2003. The columns look like this:

| W | D | 2 | S |

| b | 1 | 1 | 1 |

| c | 2 | 2 | 2 |

| a | 3 | 3 | 3 |

What i'm planning to do is to arrange the Word column [W] alphabetically but in such a way as the entries in the other 3 columns (Def 1[D], Def 2 [2], Source ) will follow the word next to them. Like this:

| W | D | 2 | S |

| a | 3 | 3 | 3 |

| b | 1 | 1 | 1 |

| c | 2 | 2 | 2 |

What i'm afraid of is that if i arrange the Word column, the other columns wouldn't follow

| W | D | 2 | S |

| a | 1 | 1 | 1 |

| b | 2 | 2 | 2 |

| c | 3 | 3 | 3 |

Note: sorry if this question has been asked. tried searching the forum but was unable to find a similar question to mine

Hi fudgeydodgey, and welcome to TSG.

First make a backup copy of the Excel file as it is.

Select all four columns and then choose the Data Sort menu option. If the first row is column headers, then activate the radio button for that in the sort dialog box. Then choose to sort on the column that has the words. The data in the same row as the given word will follow along in the sort.

Note: If you only select one column and then try to sort, Excel will usually pop up a warning that data has been found in adjacent columns that will not follow the information in the selected column. You will then have the option to have Excel automatically expand the selection to include all adjacent columns with data.

I am trying to hide rows of my worksheet that show a date in the completed column.

The orders that are complete have a date in column F up to this point I highlight them green so I know they are completed but it would be nice if they highlighted green and were hidden when a date was entered into the completed column.

Can someone assist me with creating a macro that will do this?

I am very new to macros so please forgive my lack of knowledge.

I have attached a sample of what I am doing however inf has been changed.

I am using Excel 2007

Thank you,

Hi, welcome to the forum,

I put some simple code in the Sheet's vba

Make sure you allow macro's to be run

Just enter a date in the last row of column F for testing.

For the other green rows, just click in column F and update the date by retyping it

I have a column with a number that is big - it is actually an exported worksheet. The number is a FARS account number that can be broken into elements.

I remember seeing that it's easy to do.

It would result in the following:

Before: 127010021506114300

After: 1270.100.2150.6114.300

I don't remember how she did it tho.

Thanks

https://forums.techguy.org/threads/excel-2003-column-formatting-question.1062456/

Hey everyone, I have data in the form of several rows, each of which has several words separated by ": " (colon + space) I would like each word separated in its own column, and have therefore used the Text To Columns command (under Data|Data Tools). But apparently you can only define one character as the delimiter, and if I define it to be just the colon, then I have a space at the beginning of each word. I know I can probably do a few extra steps to ge rid of that leading space, but my question is: can one define more than one character as a delimiter, under the Text To Columns command? Anticipated thanks for any help!

Well, if it is something like A: 1 then all you need to do is Text-to-Columns, Delimiter.

Choose Space and in Other enter :

Check the box that says Treat consecutive delimiters as one.

That should work for you.

I have found a few posts here and there about how to move columns around but nothing quite what I m looking for.

So I get these data dumps from a vendor weekly , column headers are always the same but they are always in different order (strange I know)

Example

Email | Phone | Last Name | First Name

I would like to have a macro cut/paste entire rows and put in a specific order

First name |Last Name| Phone| Email

Any help would be great! Thank you!

How many total columns are we talking about? Is it possible to post a sample workbook with any sensitive data removed so I can see the structure.

Regards,

Rollin

Novice to Excel... I know basics, but this is over my head... and it looks like this is the place! (now that I've buttered you up, on to my problem! )

Basically I have a sheet with all data in Column A

Bob Smith

ABC Company

123 Main St

Anywhere, USA

Phone 555.1234

Fax 555.4321

Jane Doe

XYZ Company

321 Main St

Somewhere, USA

Phone 555.0000

Fax 555.0001

Etc…

I was hoping to get:

A | B | C | D | E | F

Name | Co Name | Street | Town | Phone | Fax

Name2 | Co Name2| Street2| Town2| Phone | Fax

The Copy Special – Transpose won’t do multiple rows it ends up with everything in one row as opposed to everything in one column

(Name Co Name Street Town Phone Fax Name2 CoName2 Street2…etc)

Any help would be appreciated!

PS - WinXP and Excel 2003

https://forums.techguy.org/threads/solved-one-column-to-several-columns.485090/

I have a list of 39525 emails in column A and a list of 19909 emails in column B. If an email exists in column B that also is in column A I want it removed from column B. PLEASE HELP! THANK YOU!!!!!!

https://forums.techguy.org/threads/solved-excel-2010-remove-duplicates-from-column-b-that-exist-in-column-a.990368/

Hello,

I have a very large spreadsheet of data, 365 columns and 290 rows. I would like to convert the data into 1 very long column, with all of the data consecutively listed. Is there a function that allows me to tell the formula to look to the first line of the next row of data, and copy down from there, and then go to the next column, etc? Right now, I'm just referencing the first cell in each column, dragging down to fill the 290 following cells, and then referencing the next column. It's really time consuming because I have 48 of these spreadsheets. Any suggestions would be greatly appreciated!

Hi,

if you name the range of 365x290 cells as, for example, 'MyRange', then the following (downwards extendable) formula should work:

=OFFSET(INDEX(MyRange,1,1),MOD(ROW(A1)-1,ROWS(MyRange)),(INT(ROW(A1)-1)/ROWS(MyRange)))

Jimmy

Hi Everyone,

Seriously need your help ><"

I am using MS Office 2010 and Windows 7

I have a data like this (The number of Products and Shops are much more)

I want the result to be like this

This is the attached file for you to download the data or you can simply download from the attachment in this post

http://www.sendspace.com/file/iqvlce

I would really appreciate the help of the community

Kudos to everyone!!!!

in a new cell, create the following formula.

=A1&" "&B1&" "&C1

Or isn't this what you want?

(the cell-numbers are those which you want to combine).

You can copy the formula for all cells (haven't seen your file, find the link a bit tricky, maybe attach the file in a post here)

Hi Rollin Again and everyone in this forum Hoping someone could help me in my problem I have a workbook in Microsoft Excel Windows of about rows of words in different languages Some words have different languages and some are only or all are written in Column A of the workbook Column A English ------- in color Orange Spanish ------- in color Red Italian ------- in color Blue German ------ in color Black Papiamento --- in color Green etc What I want is a Macros Program that can put all Spanish words red color in Column to Another (Windows) A from Column, texts Transfer 2010 Excel Column B all Italian words blue color in Column C all German Transfer texts from Column A to Another Column, Excel 2010 (Windows) words black color in Column D and all Papiamento words green color Transfer texts from Column A to Another Column, Excel 2010 (Windows) in Column E Like for example Column A Transfer texts from Column A to Another Column, Excel 2010 (Windows) --- Column B ---Column C --- Column D ---Column E English -----Spanish ----Italian ----- German --- Papiamento etc Thank you very much in advance and I ll appreciate all the helps you could give me sukisuki nbsp

https://forums.techguy.org/threads/transfer-texts-from-column-a-to-another-column-excel-2010-windows.1095770/

I need some help. I am trying to come up with a formula that counts the times that a "string" occurs based on a value in another column.....

Example:

Everytime column a = "Jones", check if column e = "tom".....if so count it.

The EASY way to do this is to concatenate columns A and E using (for instance) this formula:

=A1&E1

or

=A1&" "&E1 (puts a space between two words)

Then count the individual field. Make sense? Will it work for you?

------------------

~dreamboat~

Brainbench MVP for Microsoft Word

Brainbench

I'm trying to do a simple 2 column merge in an Excel file. The idea is to put both first and last names (currently in 2 separate columns) into one column. There is more data in the worksheet, address, telelphone etc - in other columns.

When the name columns are selected, then FORMAT CELLS, then ALIGNMENT, MERGE CELLS, it comes back with -

"The selection contains multiple data values. Merging into one cell will keep the upper-left most data only"

There's text only in all the cells in both columns.

When I click on OK - like yea, do it anyway - both first and last name columns are blank - and there is no merged column.

I have also tried to use the CONCATENATE function and it works great for 1 cell at a time, but does not do entire columns...

Is there another step I've missed in this ?

Thanks...

COLUMN A

5

5

6

9

COLUMN B

A

A

B

B

A=10

B=15

Hi there, I need help creating a formula that adds Column A, only if Column B = 'A'.

I then need to multiply that counted value by another cell.

Can someone please help me? I think I'm over-thinking this.

=SUMIF(B1:B4,"A",A1:A4)

Where B1:B4 is where your A and B values are, and A1:A4 is where your numbers to sum are located.

HTH

I have a spreadsheet that I would like to give to both English and Spanish speaking workers. Is there a way to convert the English text in column A to Spanish in column B? I will be changing the text in column A on a weekly basis and will need to translate then distribute to all workers.

Thanks in advance.

https://forums.techguy.org/threads/excel-translate-english-in-column-a-to-spanish-in-column-b.1132933/

Ok I am trying to print a spreadsheet format list. I have about 30000

title-artist entries in 2 columns. I am basically trying to print pages 1 & 2, 3-4, 5-6 and so on the same page without shrinking them way down. I want it to be just like I printed 2 columns but with 4 instead. I want to use the left side of the page for page 1 and the left side for page 2. Any help would be greatly appreciated.

Michael

Welcome to the board.

http://www.asap-utilities.com/asap-utilities-excel-tools-tip.php?tip=124&utilities=Format ?

I have three lists of data that need to be combined into one longer list. Each list has the potential to have over 1500 entries with data duplicated in the other lists. I have been combining the data into one list by copying and pasting, but I want to speed up the process by having a macro that can do that and remove duplicates (unless there is an easier, faster method). I have figured out the part of removing the duplicates, but the real problem for me is being able to combine the three lists into one when each list is dynamic and can be longer or shorter on any given day. I am using excel 2003. Any help would be great!

https://forums.techguy.org/threads/solved-combining-three-columns-into-one-longer-column.767824/

Hi

Created a 5x1 Table , in one of my columns the text is slightly higher than the rest in the table, how do i get it inline?

Thanks

I am using Microsoft Office Word 2007

https://forums.techguy.org/threads/text-in-column-is-slightly-higher-than-the-rest-of-columns.748005/

Hi,

Using Excel 2003, I have three series of data and I'm trying to create a line-column chart. I can easily get two of the series as columns and one series as a line, but I need it the other way round, with just one set of columns and two lines. Is this possible? I've spent nearly an hour right-clicking and going into every option and format I can think of but I can't make it work.

I'm attaching a mock-up of the kind of thing I need, as it may be easier to visualise than to explain.

Thanks

I just selected each type of data and chose for a line chart type for the two series and a bar type for the widgets

I hid all but three columns and if I right-click on column A to insert a column, it becomes column B and no column is to the left.

I have Excel 2007

I put the cursor in column A, right click, and then insert.

Insert entire column. It appears to the left. Is this what you are doing

Dear all How do I sort a column with quot quot according and Sort column other number a with Excel: column keep #.#.# to descending Excel: Sort a column with #.#.# number and keep other column values in excel while keeping the other columns on Excel: Sort a column with #.#.# number and keep other column the same row Number range is lt gt Example for columns B A V K P C G H D L J M S X Required Output sort only second column D A C G J B K M L P S V X H Code can sort by ascending but does not pull the column Code Sub sortColumn Dim arrData As Variant Dim i As Long j As Long Dim temp As Variant 'Range name is quot ID quot arrData Range quot ID quot CurrentRegion Value For i To UBound arrData For j i To UBound arrData If getDesc arrData j arrData i Then temp arrData i arrData i arrData j arrData j temp End If Next j Next i Range quot G quot Resize UBound arrData Value arrData End Sub Function getDesc a As Variant b As Variant Dim aWords As Variant bWords As Variant Dim i As Long aWords Split a amp quot quot quot quot bWords Split b amp quot quot quot quot For i To LT Val aWords i lt Val bWords i If Val aWords i lt gt Val bWords i Then Exit For Next i End Function Appreciated any help nbsp

http://www.pcreview.co.uk/threads/excel-sort-a-column-with-number-and-keep-other-column.4069441/Hi,

I have a query but I'm not sure if it's even possible to do.

If I enter a name into cell J1 I want to check for this name in column H then for each row that is found on I would like to sum column B and output the result to cell J2. E.g. If I enter Paul into J1 I would like to search for this name in column H. For every row this is found on I would like the B cell for that row to be included in a sum of which the result is output to cell J2.

Is this possible and if so does anybody know how?

Cheers

Paul

yes, SUMIF() should do that

i'll edit - and add an example

enter into J2

=SUMIF(H2:H9,J2,B2:B9)

see attached example file

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.

https://forums.techguy.org/threads/xls-macro-to-move-data-to-columns-delete-empty-rows-delete-duplicate-columns.1014294/

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

https://forums.techguy.org/threads/solved-excel-question-removing-unwanted-rows-moving-some-rows-to-new-columns.814184/

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)

State,Zip(F4)

Home Phone(F5)

Work Phone(F6)

Email(F7)

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.

bump

Hi all I got a problem on my desk wich i just can t find a good formula for and i m starting to think its impossible but at that point I click on my techsupportguy link and voila So here is the problem I got a file on wich the first sheet is one with a number of columns Firm Adress Phone Fax Date of entry Website and email Now the person who made the file was so clever to put that data Solved: Excel Question Data Get adress and all in one sheet per firm So i got about firms on that list without the other data and next to it a link to the sheet with all the data on still with me i hope What I need to do now is try to get the data from those sheets all into one sheet For example There is a firm FIRM and in the cell next to it is a link to the sheet FIRM and in that sheet is all the data always in the same order luckily I need to get the data from that sheet and paste it in cells next to the cell FIRM on the first sheet So sheets have to be transformed into sheet Anyone who has ideas on how to do that without manually looking everything up and copy-paste Solved: Excel Get Data Question it is more then welcome Thanks in advance Stefandh nbsp

https://forums.techguy.org/threads/solved-excel-get-data-question.694841/

I have floppy disks on which I annually update approximately Excel files Each of these files can be printed out on Question Retrieval Data Excel an quot x quot page These floppys are all years old Not sure who the manufacturer is but they re IBM formatted HD with the AT amp T logo What s happened is that on some of the disks these Excel files simply won t open or Excel Data Retrieval Question will try to Excel Data Retrieval Question but can t quite open I ve been getting as many as three different types of messages with regard to this problem such as quot Unable to open quot with the big red circle or quot File is available in quot read-only quot format or driver is unavailable quot or quot File is in comma delimited format quot where I can see some symbols in the background and I m asked to make some choices My computer is a year old Compaq Presario I m guessing that maybe some of these floppys have deteriorated due to age or lack of regular use I m wondering if the Excel files on the quot problem quot disks can be somehow copied or transferred to another floppy or perhaps a CD-Rom My alternative is to re-create these files from scratch Is this a job for a quot data retrieval service quot Any ideas would be greatly appreciated Vinnie Ralph nbsp

https://forums.techguy.org/threads/excel-data-retrieval-question.285162/Hi,

I want to merge two excel files into a 3rd one.

The data are in the files Book1, Book2 and the result in Book3.

I know that i can use VLookup to do the above. But what i have pasted is just an example. The real excel files contains about 9 columns with at many repeating row!!!

Any help will be much appreciated.

Thx

Hi welcome to the board. How are you going to treat the duble items, If you want to merge 2 files adn duplication occurs here must be one cell whihc is common to let's say Book1 and Book2.

Do you have a common value? Index? Whatever you call it?

Hi All,

I have a question about data massaging.

I have a Word file, with several data elements like the following:

John Doe

111 E. 5th St.

New York, NY, 12345

(216) 555-1234

I'd like to convert that file to Excel, with one column each for Name, Address, City, State, Zip Code, Phone number.

What is the best way to do this? Is it possible to do it using simple Microsoft Office applications?

Thanks

123champ

https://forums.techguy.org/threads/data-massaging-question-from-word-to-excel.412256/

Hi guys,

I have a raw data spreadsheet of 10 stocks' price with date. I would like to write a vba program that can read off the raw data, produce 1st column as the date and the next 10 columns as the stocks' price. If any date doesn't have at least 1 of the stock price, that date will be obmitted.

It would be greatly appreciated if you can guide me through this since I am pretty new to this vba programming.

Thanks a lot!!

would you be able to post an example of the data - dummy - ideally in an spreadsheet attached here

so we can see the format of the raw data and how you want it to look

Hi guys I have a problem I am hoping some one can help with I am using Excel and this is my dilemma I have a cell 3 range 1 Column into true" other "if columns Compare a Concatenate large spreadsheet of info rows In column A I have a range of customer ID s many are duplicated see example each time a ID is duplicated it means that there are cells in other columns with related information A B C D D Smith Compare a Column range "if true" Concatenate 3 other columns into 1 cell note C Green note D Smith note C White note C White note D Smith Note C White note What I am trying to do is compare each cell in column A with the next cell Compare a Column range "if true" Concatenate 3 other columns into 1 cell in column A If the customer ID is the same then concatenate the multiple cells from columns B C D into Column E A B C D E D Smith note D Smith note Note note C Green note D Smith note So as in my example if there are occurances of the exact same Customer ID then it would concatenate the Information in the corrresponding Columns B C D and Rows into one new cell in the same row as the first instance of the Customer ID Column E and Row What I have so far is this IF A A CONCATENATE B C D B C D but it only compares two cells then concatenates those two rows so I end up with a lot of duplicated data in column E I hope that is clear nbsp

https://forums.techguy.org/threads/compare-a-column-range-if-true-concatenate-3-other-columns-into-1-cell.703131/

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

https://forums.techguy.org/threads/solved-in-xcel-i-need-to-combine-multiple-columns-containing-multiple-rows-into-one-column.475772/

I think my 8 year old nephew is better at Excel 2003 then I am, but he's in school so I can't ask him. I hope someone on this board can help!

I want to paste a table of stock trade data onto a sheet, and then run a macro that will look up the trades with today's settle date only, and then paste the trade money for that account into a specific cell on another sheet. My Excel help book is not very clear on the best way to do this. I can't use VLOOKUP because the settle date is in column 7, and the dollar amount is in column 10.

thanks!

https://forums.techguy.org/threads/excel-2003-question-how-can-i-populate-only-todays-data-on-another-sheet.716780/

Hi there -

Thanks in advance for helping.

Here's the situation:

I have TWO excel files

'FILE A' ... Has a full list of over 2,000 entries, each with a unique ID in the far-left column ('A')

'FILE B' ... Has a list of 400 entries with their respective IDs (that are found within the 2,000)

I need to compare the data found in File B with that in File A. If they appear in both files, I want a '1' to appear in the cell next to it

Here's what I need:

In Column 'B' of "FILE A", I need a formula that will do the following:

=If A2 is found in the first column in "FILE B", then show '1', if not show nothing

Does anyone have a solution? I am not certain if a VLOOKUP is the possible solution, and if so, I cannot figure out the correct formula.

Thanks again,

fisher1711

I am not certain if a VLOOKUP is the possible solutionClick to expand...

should be and then use a IF statement - to test if the vlookup is true or not

something like

=VLOOKUP(A2,[fileB.xlsx]Sheet1!$A$2:$A$xxxxx,1,false)

where xxxxx is the end of the row range

will do the lookup

use ISERROR to test if true of false

and then an IF to add the 1

=IF(ISERROR(VLOOKUP(A2,[fileB.xlsx]Sheet1!$A$2:$A$xxxxx,1,false)),"",1)

are both files open ?

this is just on two sheets, as an example here

=IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$A$4,1,FALSE)),"",1)

so if there is an error - ie cannot find the lookup then as its true it returns nothing "" if it is true it returns 1

Hi I am trying to build a harvest data sheet for wildlife management implications and I cannot find out how to auto-populate data from the initial sheet to separate sheets based on the entry in one specific column Let me explain the data sheet in more detail I have the initial sheet set with columns containing harvest information for individual animals and broken up by individual properties Property quot X quot followed by date age weight sex etc in columns for each specimen harvested Now what I would like to be able to do I would like to enter the data under each property the first time and have three other sheets I do auto-populate Question: to Excel How data inidividual sheets? 2007 auto-populate i e -total data set not divided by property -all males -all females based on the entry in the quot sex quot column- M or F I hope I have made the problem clear enough to understand I know there must be a way to do this Please help Thanks nbsp

https://forums.techguy.org/threads/excel-2007-question-how-do-i-auto-populate-data-to-inidividual-sheets.1033235/

I can't figure out how to add a column to the right of all my data. I insert column, and it inserts a column LEFT of the column my cursor is in (the very last column). And I haven't figured out how to manipulate the columns to move them around like I can in Excel (which is still a hit or miss thing for me - anyone have the tip on how to do that, too?)

For any curious, I did find a way... You have to open design view and add it in one of the blanks beneath your last entry.

Not quite as easy as just right-click > insert, but it works.

Hi,

I want to know how i can export the data from Access to excel using Access VBA for the specified sheet using data linkage with access database. Like we used to do it manually in excel as external data from access.

So that the excel size won't be that big and also it saves processing time.

https://forums.techguy.org/threads/solved-access-data-export-into-excel-as-the-data-linked-to-excel.1073233/

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

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.

http://www.computing.net/answers/office/moving-columns-data-into-new-row/20216.htmlAlright 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

AJ

Hi guys,

Is it possible to hide certain columns in a worksheet? (Excel 97 on Win98se). We have a spreadsheet that has columns A-G then BF-BJ, then BM-BR. The person that created the spreadsheet is on vacation this week, so we can't talk to them. The person using the spreadsheet now, says there is more information that is needed to get this information currently on the spreadsheet. Is it a reach to think that there may be more info in possibly hidden columns? Aside from renaming columns, is there any other way that someone would go straight from G to BF?

Thanks a lot for the help!

J

Hi Guys,

Ive opened my excel this morning and instead of there being numbers down the side and letters across the top ive got numbers down the side and across the top so i cant do any statements? Does anybody know what this could be, and why its happened?

Thanks

Hi gazmysta,

Tools/Options/General untick the R1C1 reference style box.

BR

Hew

I have some data on Excel. Address numbers are in one column and the street names are in another column. Is there a way I can copy all the information in one column (address numbers) and merge it into another column (street names)?

Rob

if the address numbers are in a1 and the street name is in b1, paste =A1& " " &B1 in c1. This will place the number and address in c1.

Tom

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

https://forums.techguy.org/threads/vba-search-two-columns-same-time-and-get-data.901729/

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?

In the first Criteria row for each period column enter

>0

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

https://forums.techguy.org/threads/solved-vba-compare-data-columns.1125060/

Hey,

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.

Cheers

https://docs.google.com/spreadsheet/ccc?key=0Av0-qDbMrH0rdHJWaWlReXd4RXptWlpwME50UTBsclE

https://forums.techguy.org/threads/moving-data-from-columns-to-rows.1041255/

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

https://forums.techguy.org/threads/extract-sql-data-to-specific-columns.1096904/

Afternoon. I would like to build a query in Access 2010 to move some data from one field to another.

I only want to move some data though.

My fields are named mailadd1 and mailadd2

The data that needs to be moved is in mailadd1.

These are addresses. The first field should be the street address, the second should contain the city, state, and zip.

This is correct on a lot of the rows, but on about half of the 109K records, the data that should be in mailadd2 is in mailadd1 and mailadd2 is blank.

So I need to move the data from mail1 to mail2 IF mail2 is empty. In the following example the first row and the last row's data need to be moved, the middle two rows are fine.

Again, mailadd2 is always blank if the data needs to be moved, if that helps.

Thanks in advance for any help you can offer.

i have a folder on my second drive that is for music. when i right click in there to arrange the folders it doenst give me the usual options. it has name, size, type, artist, album title, year, track number, duration. i like to arrange them by when they were put in the folder, by the modified option. cant figure out how to set it back to the way it was or atleast include the modified option in there. anyone know how to change this?? it doesnt do this in all folders...just some??

Go to View > Choose Details, put a check in 'Date Modified'.

I have three columns of information. Column A is 900 part numbers with no prices, Column C is 600 part numbers matched with their respective $ value in Column D. I need a formula to match the part in column A with the like part in column C and find the $ value from Column D and place that $ value in Column B next to its respective part number. Can anyone help me with this? Thanks so much!!

I am trying to insert a column in my spreadsheet, but I keep getting the following error message:

To prevent possible loss of data, Microsoft Excel cannot shift nonblank cells off the worksheet.

I don't know why I continue to get that message in this particular spreadsheet. I tried copying and pasting in a new workbook, but it still doesn't insert the column.

Does anyone know what could be wrong?

Never mind. I was finally able to insert the column. I still don't know what the problem was, but I tried copying and pasting again and it worked.

if I click on the column at the very top it will highlight the column, then I can drag and drop it somewhere, but it overwrites the column where I dropped it. how do I keep it from doing that?

its a CSV file im working with. I have another CSV viewer where I can easily drag and drop columns. but the problem with that one is the first line is not comma seperated, so I have to open it with notepad and remove the first line before I can view it errr...

Create a blank column into which you can drag/drop the target column

letchworth

I need to sort columns in Microsoft excel but here is my dilemma I am having. I need them to be sorted based on the first column alphabetically. Which I understand requires just hitting the sort button.

However.. my grid looks something like this

NAME Hours Address

Tom 6 10 B Street

Bob 3 30 Smith Lane

I want to sort by Name alphabetically. But I want the addresses and hours to also sort along with it. So for example when Tom's name is moved below Bob's I want his hours and Address to also move with it. So everything stays organized. How would I do that?

Select all three columns before doing the sort (DATA --> SORT)

Regards,

Rollin

Please help. I need to find a formula that compares any value in one column to any value in another. so for example -

A B

1 3643 6488

2 5378 9087

3 9631 0092

4 4657 9876

(repeat for several hundred lines)

i need a way to find out if any of these are duplicates. i've tried a few formulas i've found on this site but all i've got back is #NAME? when entering them. also, i'm using openoffice, not excel. does that matter? thanks.

This is nothing special. There's a butt-ton of ways.

Here's my article on it.

http://www.officearticles.com/excel/dealing_with_duplicate_records_in_microsoft_excel.htm

I have a huge spreadsheet which I am trying to analyse but I really need some help! There is one row of data for each company, then separate columns for products. I need one single column so I can use functions like COUNTIF. A simplified example:

Current situation

Company 1 | Product a | Product d | Product f

Company 2 | Product b

Company 3 | Product a | Product f

Desired result

Company 1 | Product a

Company 1 | Product d

Company 1 | Product f

Company 2 | Product b

Company 3 | Product a

Company 3 | Product f

I am SURE there must be a way of doing this, but I am going round and round in circles right now.... thanks for helping!

https://forums.techguy.org/threads/excel-multiple-columns-to-one.492613/

Ok, so i have an excel sheet with a number of columns that i need a formula for to calculate another value in another column in the same sheet. Column A is the one which needs the formula. The data in Column A needs to be a string which looks like this:

GCSO00MNI000

And then needs to have additional characters added to the end which are the values found in column C, such as "107". Therefore, column A's value for that row would be GCSO00MNI000107. The same calculation needs to take place for the next row in Column A, completing it's string with the value in the next row in Column C, and so on and so on, for 6500 rows.

How do i do this and not have to retype the needed calculations for all 6500 rows?

thanks in advance.