Windows Support Forum

Is it possible to subtract data columns in excel?

Q: Is it possible to subtract data columns in excel?

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

Relevancy 100%
Preferred Solution: Is it possible to subtract data columns in excel?

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

Relevancy 102.77%

Hello!
Here's what I've been tasked with:
I'm setting up a spreadsheet to track invoices. I have 2 tabs. The first tab records the Purchase Order Number, Supplier, terms, the amount of the PO and a few other items. The second tab tracks invoices. What I hope to accomplish is this: When a user enters the invoice info - they will also enter what PO number the invoice should be applied to. I want to set up a column 'Balance of Invoice' on the first tab that will automatically look at the list of invoices and subtract if the POs match. I'm thinking Index, match will work but I need assistance. Any feedback is greatly appreciated. thank you
 

A:Excel: Match, Index and subtract across 2 columns and 2 tabs

LoriD2014,
I use a similar workbook to total entries from several sources. I use sumif. If you post the names of the spreadsheets and the ranges for the PO and invoice totals I can mock up your formula.
Stoneboysteve
 

http://www.pcreview.co.uk/threads/excel-match-index-and-subtract-across-2-columns-and-2-tabs.4066152/
Relevancy 78.69%

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.

http://www.sevenforums.com/browsers-mail/295491-web-site-data-excel-columns.html
Relevancy 77.83%

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 77.83%

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.
 

Relevancy 77.83%

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.
 

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

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

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 77.83%

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.

http://www.computing.net/answers/office/how-to-set-icon-in-excel-by-comparing-data-in-2-columns/20422.html
Relevancy 77.83%

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

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
 

https://forums.techguy.org/threads/solved-excel-vba-moving-all-data-to-columns-a-b.1128060/
Relevancy 77.83%

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.
 

Relevancy 77.83%

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
 

A:Solved: comparing data in two columns in Excel

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
 

https://forums.techguy.org/threads/solved-comparing-data-in-two-columns-in-excel.989078/
Relevancy 77.83%

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 76.97%

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

Relevancy 76.97%

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
 

https://forums.techguy.org/threads/excel-swapping-columns-and-rows-layout-of-data.456591/
Relevancy 76.97%

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?
 

Relevancy 76.11%

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

A:Excel 2010 separating data from one column to multiple columns

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
 

https://forums.techguy.org/threads/excel-2010-separating-data-from-one-column-to-multiple-columns.1090901/
Relevancy 76.11%

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
 

A:Excel question: how would you connect data in defferent columns into one column?

In E1 enter
=A1&"-"&b1&"-"&c1&"-"&d1
copy down
 

https://forums.techguy.org/threads/excel-question-how-would-you-connect-data-in-defferent-columns-into-one-column.934495/
Relevancy 76.11%

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 76.11%

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

A:Excel - function to sort multiple columns of alphanumeric data

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.
 

https://forums.techguy.org/threads/excel-function-to-sort-multiple-columns-of-alphanumeric-data.933917/
Relevancy 76.11%

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
 

A:Move alot of Data from Columns Excel 2010 - too big to transpose

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

https://forums.techguy.org/threads/move-alot-of-data-from-columns-excel-2010-too-big-to-transpose.981919/
Relevancy 73.96%

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?

Regards,
Rollin
 

https://forums.techguy.org/threads/visual-basic-macro-for-excel-2002-columns-to-rows-in-new-sheet-using-unbalanced-data.1080037/
Relevancy 71.81%

What is the formula for subtracting in excel? I figured out how to add...now need to subtract.
 

Relevancy 70.09%

In Cell A1 I have my daily target say 1000when i enter number in a2,a3,a4 etc the number should get deducted automatically. for exampleI have 1000 in A1 when i enter 50 in A2 it should give me 950 and when i enter 70 in a3 it should give me 880 (1000-50-70) and when i reach 0 it should give me a message target achieved.

A:how do i subtract from a constant cell in excel

I can think of 2 methods, one using VBA (a macro) and the other using Conditional Formatting.Macro Method:Right click the sheet tab for the sheet you want this to happen in. Paste this code into the pane that opens. Whenever you enter a value in A2 or below, the SUM of the values below A1 will be subtracted from 1000 and the value will show in A1.
Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made to Column A
If Target.Column = 1 Then
Application.EnableEvents = False
'Determine how many values are in Column A and Subtract SUM from A1
lastRw = Range("A" & Rows.Count).End(xlUp).Row
Range("$A$1") = 1000 - WorksheetFunction.Sum(Range("$A$2:$A" & lastRw))
'Display message if A1 <= 0
If Range("$A$1") <= 0 Then MsgBox "Goal Reached"
Application.EnableEvents = True
End If
End Sub
Conditional Formatting Method1 - Place 1000 in a cell other than A, e.g. B1. You can hide the cell or change the text to White if you don't want to see the 1000.2 - Enter this formula in A1 if using B1. Adjust to fit your situation. =B1-SUM(A2:A100)3 - Enter your message in a cell that will always be within the viewable area of your screen, e.g. C1.4 - Format the cell as White text to hide the message.5 - Conditionally format the cell using the following formula, and use a format to fill the cell with a color of your choice. When the formula is TRUE, the White text will appear within the filled background:=A1<=0Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

http://www.computing.net/answers/office/how-do-i-subtract-from-a-constant-cell-in-excel/20359.html
Relevancy 69.23%

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

https://forums.techguy.org/threads/excel-error-merging-columns-multiple-data-values.1018600/
Relevancy 68.8%

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
 

A:Solved: Printing 4 Columns from 2 columns in excel

Welcome to the board.

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

https://forums.techguy.org/threads/solved-printing-4-columns-from-2-columns-in-excel.773525/
Relevancy 68.37%

Hi guys I need to write a macro that is connected to many buttons one for each row in spreadsheet ask for a value to subtract error check and if ok subtract from a corresponding value in column P Subtract based button VBA: from a cell corresponding placement Excel on When the button on a left hand side is clicked a dialog button should pop-up prompting a user to input the number Excel VBA: Subtract from a corresponding cell based on button placement of pieces to subtract from the corresponding cell row based on the row of the button column P error check the Excel VBA: Subtract from a corresponding cell based on button placement input number show error if the value is smaller than or larger than the corresponding value I can write the error check by myself so no need to do this step subtract the number from the corresponding cell i e user clicked a button located in cell A input pieces in a dialog box and there are pieces in P gt the pieces in P should change to Thank you for helping me out Hope the description is not very confusing Michal nbsp

A:Excel VBA: Subtract from a corresponding cell based on button placement

https://forums.techguy.org/threads/excel-vba-subtract-from-a-corresponding-cell-based-on-button-placement.1135464/
Relevancy 67.94%

I tried searching for this topic but it s obviously another set of search terms that I obviously have no idea in recreating so I m sorry if I m double-posting for a set of principles that are probably already established Running Microsoft Excel on Microsoft Windows XP SP So here s the idea I ve got thousands of records where many of them are going to be duplicates The way I of Between Records Unique Excel in Solved: Spaces Subtract Difference m manipulating the data follows these steps Sorting the Column by Lowest Value to Highest Value or Alphabetically Placing a Series Column Next to the Just Sorted Column Perform a Filter on the Duplicate Records with these options quot Filter the list in place quot with the option of quot Unique records Solved: Subtract Difference of Spaces Between Unique Records in Excel only quot Then Subtract the Difference Between Each Unique Record s Series I ve attached a spreadsheet to demonstrate what I Solved: Subtract Difference of Spaces Between Unique Records in Excel m looking for I would manually do this if I only had a few records to do this with but I m still resulting in several thousand records even after performing the quot Unique Filter quot function To put it simply I know how to perform this in the manually laborious method that would require several hours of my time but I need something that can turn it into a several minute process Once again I apologize for repeating a problem that s probably already been answered Thanks in advance nbsp

A:Solved: Subtract Difference of Spaces Between Unique Records in Excel

https://forums.techguy.org/threads/solved-subtract-difference-of-spaces-between-unique-records-in-excel.925047/
Relevancy 66.65%

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

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

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.
 

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

bump
 

https://forums.techguy.org/threads/solved-move-data-from-rows-and-columns-to-other-rows-and-columns.932353/
Relevancy 58.91%

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.
 

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
AJ
 

https://forums.techguy.org/threads/match-columns-and-data.1169717/
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.

http://www.computing.net/answers/office/moving-columns-data-into-new-row/20216.html
Relevancy 57.62%

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
 

A:Excel columns?

Hi gazmysta,

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

BR
Hew
 

https://forums.techguy.org/threads/excel-columns.324254/
Relevancy 57.62%

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
 

A:Excel Columns

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
 

https://forums.techguy.org/threads/excel-columns.93109/
Relevancy 57.62%

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
 

Relevancy 57.62%

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
 

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

https://forums.techguy.org/threads/access-hide-row-if-many-columns-contain-same-data.986072/
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%

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 56.76%

I would like to merge two columns in excel, to create a third column that has alternating cells originating from the first and second column.

To make it a little clearer: I have values in column A and B

In column E, I would like to see the following result:
E1 = value of A1
E2 = Value of B1
E3 = value of A2
E4 = Value of B2
E5 = value of A3
E6 = Value of B3
...

The attachment to this post contains the result I want to obtain.
 

Relevancy 56.76%

I need to create this table in Excel, How do I create table like I attached? How do I divide 2 row into 2 columns. Im using mac 2013 office.
please help thx

A:how to divide row into 2 columns in excel

Not sure I follow what you need to do, but....

highlight a group of cells in a row, such as 3A, 3B, and 3C.

Then right click and choose "format cells". Then choose the alignment tab. Then choose "merge cells".

That will combine 3A, 3B, and 3C into a single cell.

Is that what you are trying to do?

You can do the same thing with cells in a column--combine them into a single cell.

You can do that with any combination of cells.

You can then type in whatever you need into a cell and align that typed text to either the left, right, or center of the merged cells.

http://www.sevenforums.com/microsoft-office/337969-how-divide-row-into-2-columns-excel.html
Relevancy 56.76%

My excel has columns ad infinitum. I only need through column J. How can I get rid of the other columns so that I can print only through J? Thanks to all who know so much and take time with folks like me who know so little.

A:deleting columns from excel

you should be able to set the print area
what version of excel do you have

I have moved to the office forum

http://www.techsupportforum.com/forums/f57/deleting-columns-from-excel-681621.html
Relevancy 56.76%

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.
 

Relevancy 56.76%

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

A:moving columns in excel?

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

letchworth
 

https://forums.techguy.org/threads/moving-columns-in-excel.555855/
Relevancy 56.76%

How do you alter column placemens in Excel so that Column 2 changes places with Column 1?
 

A:Transposing Excel Columns

if I understand you correctly, all you need to do is to cut column A (rows are numeric, columns alphabetic), click on the column after column b, and click insert cut cells.

again, that is if I understand you correctly. Please advise if this works.
 

https://forums.techguy.org/threads/transposing-excel-columns.414182/
Relevancy 56.76%

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?
 

A:Sorting Columns in Excel

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

Regards,
Rollin
 

https://forums.techguy.org/threads/sorting-columns-in-excel.591076/
Relevancy 56.76%

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?
 

A:Excel....inserting columns

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.
 

https://forums.techguy.org/threads/excel-inserting-columns.226144/
Relevancy 56.76%

I'm using Excel 2010.
I have a workbook with TWO Tabs (sorry, cannot attach).
First Tab, the data - Second Tab, the form I want generated.

In the first Tab, Column A is a date. Columns B and C are TEXT Data
For the second tab, I want to create formula that reads like this:

If first tab Column A = a specified date, then bring in the information in first Columns B and C.

If the date appears five times, can it bring the information in and create five rows? (or am I restricted to only cell to cell formulas?)
It sounds like I should just do a simple cut/paste sort of thing and not create a difficult formula, but I really need a formula, because ultimately it will be expanded beyond two simple tabs.
 

Relevancy 56.76%

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

thanks,

drew
 

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
 

https://forums.techguy.org/threads/excel-columns-to-rows.210075/
Relevancy 56.76%

Please HELP,

This problem's going way beyond my excel knowledge. and beyond my friend "ASAP utilities"
Can someone please tell me how to merge 2 Column using a key factor " i.e number".

Here I have 4 column's total.
The left 2 have the correct order/ The Right 2 are in the wrong order.
I need to fix that using their common factor/#.

I have attached the file here.

I would be very happy to fix this ASAP.
How do I do it???

Thanks

Brion Dublin
 

A:help merging columns in Excel 03 !!!

I have made some mods to your sheets as follows

I created a reference table by
extracting the number part from column E using Find looking for a space and then using Mid to select the number - see the cacl for ref table
Then created a reference table tab - to use as a lookup
Then on sheet1 used vlookup to get the column D & E to align with column A
 

https://forums.techguy.org/threads/help-merging-columns-in-excel-03.688943/
Relevancy 56.76%

I would like to do a loopy using Macro.xls
Let say,
1-start at column 13 do sorting ascending
2- find duplicate ( macro FindDup())
3- next column ... until column IV
and her my Macro record modify by me (!!)

Dim y As Integer
Dim rng As Range

For y = 13 To 255
rng = Cells(1, y).Select
Rows("1:3013").Sort Key1:=rng, Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Application.Run "CP.xls!FindDup"
Cells(1, y).Select
Next y
End Sub
I get error :
Run time error 91.(object variable or With block variable not set.
Any help ?? Thanks in advance.
 

Relevancy 56.76%

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.
 

A:Excel compare columns

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
 

https://forums.techguy.org/threads/excel-compare-columns.773950/
Relevancy 56.76%

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!
 

Relevancy 56.76%

I am having trouble adding the sum of a row across multiple columns I have the code to add teh sum from a single columns but I can't figure out how to make it so it will add the columns up and place the sum on the first empty row The number Multiple Excel of a across Need sum Columns row VBA: of cells in each row is not the same so I need to be able code for that This code is what I use to sum the rows at the end of the column but like I said earlier I need to code it Excel VBA: Need sum of a row across Multiple Columns so it will go to the end of the row while adding all cells in the row from multiple columns Option Explicit Sub StatsEachColumn Dim LastColumn As Long LastRow As Long Excel VBA: Need sum of a row across Multiple Columns lngColumn As Long LastColumn Cells Find What quot quot After Range quot A quot SearchOrder xlByColumns SearchDirection xlPrevious Column For lngColumn To LastColumn LastRow Cells Rows Count lngColumn End xlUp Row With Cells LastRow lngColumn FormulaR C quot SUM R C R quot amp LastRow amp quot C quot End With Next lngColumn End Sub

A:Excel VBA: Need sum of a row across Multiple Columns

Forgot to mention that it will need to loop since there are 4 different rows so far and it will need the ability to work with any added rows

http://www.techsupportforum.com/forums/f57/excel-vba-need-sum-of-a-row-across-multiple-columns-1070562.html
Relevancy 56.76%

Has anyone ever encountered missing columns in Excel? I received an Excel file from a co-worker and the columns are lettered from A to H, then L, then from T on. I have never seen a spreadsheet with missing columns before. I am having problems printing the spreadsheet because it cuts off after the L column. My guess is that there is a page bread in one of the missing columns. Can anyone help me with this?
 

A:Missing Columns in Excel

Click on the top left box, where the rows and columns meet. That should select everything. Then right click on one of the rows or columns and choose unhide rows/columns. That's it

BTW
:wave:Welcome to TechSpot:wave:
 

http://www.techspot.com/community/topics/missing-columns-in-excel.28333/
Relevancy 56.76%

This should be a piece of cake, I just know it! But me and excel never see eye to eye. I have a simple document with column a and column b. I want to find the matching ones and create a column C with the ones that are not matched. Can I do that? Without using copy paste for three hours?? I attached the file below.

Thanks!!
 

Relevancy 56.76%

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!!
 

Relevancy 56.76%

My english is bad. But try to understand and help me!!!

I need more columns to Excel 2000 or XP. The max account is 256 columns and I need 500 columns.

Windows 98 or 2000.
Excel 2000 or XP.
 

A:I need more columns EXCEL 2000/XP

Hi

I'm afraid you're out of luck. 256 is all you've got. It's debatable whether MS will increase this in the future, but don't hold your breath.

Just curious, why do you need 500 columns? Perhaps there is another way round your problem?

Regards
 

https://forums.techguy.org/threads/i-need-more-columns-excel-2000-xp.305967/
Relevancy 56.76%

If you have Excel data on columns A,B and C sorted by column A, is there a simple way of transposing the columns so you can view the columns in the order of B,C,A sorted by column B, without rearranging the columns by dragging them to new locations?
 

Relevancy 56.76%

I am having trouble figuring out how to split some data into separate columns. I have a column that holds numeric dates (example: 01/01/04) - I would like to use the text to columns tool to split this into 3 columns (example: column 1 "01", column 2 "01", and column 3 "04"). It looks like it should be able to do it from the example grid, but it is not working. Can anyone give me step by step instructions with which boxes to check etc...?
 

Relevancy 56.76%

Hi I have data on the semiconductor industry of companies and their partners alliances for the years - and - The data is in columns in the following format Alliance ID Company Name Company ID Partner ID Partner ID Partner ID Partner ID The above is for - and - The column Alliance ID assigns a unique ID to each alliance For example if the company Intel has a partnership with other companies Partner ID and Partner ID then this alliance is given an alliance ID XXXXXX It is important to note that this ID is for a given combination of partners i e the same company Intel can have another partnership with some other company different partner ID then this Columns Matching Excel in alliance gets a different alliance ID albeit it being for the same focal company Intel However I only have alliance IDs for the - data and need to generate them for - In other words if a given alliance in - is repeated in - then I need Excel to automatically assign the - Matching Columns in Excel Alliance ID to the corresponding row in - To illustrate In - I can have the following alliance-Intel partnering with Matching Columns in Excel Kodak and Hitachi partner ID and partner ID respectively This alliance can be repeated for - Intel with Kodak and Hitachi Then I want Excel to assign the - Alliance ID to the empty cell of - Alliance ID of the SAME alliance Is there a command or macro program that can do this automatically Thanks Mithuna nbsp

A:Matching Columns in Excel

Can you please upload an example of your spreadsheet (without any sensitive data) so we can see what we are dealing with.
 

https://forums.techguy.org/threads/matching-columns-in-excel.842988/
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%

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

Bill
 

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.
 

https://forums.techguy.org/threads/transposing-column-data-into-multiple-columns.722519/
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
 

https://forums.techguy.org/threads/data-in-columns-formula-to-generate-a-new-column.1075657/
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

BCell.EntireRow.Copy

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
Else
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
Else
IsMissing = False
Exit For
End If

Next BCell

End Function

http://www.computing.net/answers/office/compare-columns-on-different-worksheets-and-add-missing-data/19897.html
Relevancy 56.33%

I decided to hide the first couple of columns in the spreadsheet. But for some reason I can't get them back. I tried selecting the first visible column, the whole spreadsheet, the View menu. Nothing. The columns won't unhide. And yes I need their data.

For the record, hiding and then unhiding works fine when it's not the first column.
 

Relevancy 56.33%

I am trying to do a macro to multiply the several columns values, observing determined terms, but do not be getting. Attachment, worksheet with example and for macro that I typed but is not solving. I thank any help.
 

Relevancy 56.33%

Hi I have been struggling with this for a few days now I have very basic marco knowledge so be gentle I have a macro Sub Rectangle Click If Range quot D quot Date Then Range row fixed incrementing in excel a columns with quot D quot AdvancedFilter Action xlFilterInPlace incrementing columns with a fixed row in excel CriteriaRange Sheets quot Sheet quot Range quot A A quot incrementing columns with a fixed row in excel Unique False Else If Range quot E quot Date Then Range quot E E quot AdvancedFilter Action xlFilterInPlace CriteriaRange Sheets quot incrementing columns with a fixed row in excel Sheet quot Range quot B B quot Unique False Else If Range quot F quot Date Then Range quot F F quot AdvancedFilter Action xlFilterInPlace CriteriaRange Sheets quot Sheet quot Range quot c c quot Unique False Else If Range quot G quot Date Then Range quot G F quot AdvancedFilter Action xlFilterInPlace CriteriaRange Sheets quot Sheet quot Range quot D D quot Unique False Else If Range quot H quot Date Then Range quot H H quot AdvancedFilter Action xlFilterInPlace CriteriaRange Sheets quot Sheet quot Range quot E E quot Unique False Else End If End If End If End If End If End Sub which sort of works but rather than writing it out hundreds of times can I loop it Thanks nbsp

Relevancy 56.33%

I am very new to writing macros and I'm trying to find a macro that will search for a key word within the whole spreadsheet. If the keyword is found in say 7 of the rows, those rows will remain and all others will be hidden (except for the first 4 rows as these have headings, etc.).I would be very grateful to anyone who can point me in the right direction!message edited by Adrian82

A:Excel Macro - Keep only columns with key words in them

I do not have access to Excel or VBA at the present time, so the best I can offer at this point is to suggest that you look up the VBA Find method via Google. You'll find various options for the use of Find and FindNext. Those are the methods that I would use to loop through your sheet, searching for the key word.If I were to write code to do this, I would start by hiding all Rows (except for the rows with the headings) and then unhiding the rows where the key words were found. That way is easier than hiding rows which don't contain the keyword. Finding something with VBA and unhiding the row where it was found is much easier than not finding something and then hiding the row where it wasn't found.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

http://www.computing.net/answers/office/excel-macro-keep-only-columns-with-key-words-in-them/19926.html
Relevancy 56.33%

I am currently working on a large excel sheet for my master thesis The sheet consists of monthly information about companies since There are categories of data Earnings per share 3 Sorting time a by columns Excel. at row in data per month is one of the data variables I would like to sort the data Sorting 3 columns at a time by row in Excel. per month Sorting 3 columns at a time by row in Excel. with the purpose of creating portfolios with an equal amount of stocks in each portfolio I want the stocks with the most positive earnings revision to form the first portfolio and the stocks with the second most positive earnings revisions to form the second portfolio and so on each month In other words my objective is to sort the company data by row so that the company with most positive ernings per share revision per month will be to the left in the spreadsheet The problem is however that since there are data categories I would like these categories to stay together side by side even after the sorting by row Regards nbsp

Relevancy 56.33%

I have always used microsoft works and to make a list of names, addresses, telephone numbers etc. it was very easy in works data base, but I now have windows 10 and I cannot open .wdb files.
I don't know how to name columns or insert a row in excel. Do not understand their explanations in help
 

A:Naming columns in excel worksheet

http://www.dummies.com/how-to/computers-software/ms-office/excel.html

 

http://www.pcreview.co.uk/threads/naming-columns-in-excel-worksheet.4069590/
Relevancy 56.33%

I have a data base that is listed in one column and would like each address have one row and as many columns as needed.
each data set has 6-8 rows with a empty cell between each data set. I would like a macro to automate this process...
sample below:
AAA & Associates (The Arther Law Firm)
1221 Ave. of Americas,42nd Flr.
New York, NY 10020-1001
Tel212) 713-0138
[email protected]
Mng. Partner:Anthony Afther, Esq.;Law Clerk/Opns. Mgr.:Mike Murphy
www.artherlaw.com

Daniel J. Aaron, P.C.
420 Lexington Ave., Ste.902
NewYork, NY 10170
Tel212) 684-4466
Fax212) 684-5566
[email protected]
Attorneyaniel J. Aaron,PC
www.djaaronlaw.com

Abrams Garfinkel Margolis Bergson, LLP
237 W.35th st.,4th Fl.
New York, NY 10001
tel: (212) 201.-1170
Fax212) 201-1171
[email protected]
Partner:Bill Abrams
www.agmblaw.com

Ira Abrams
1839 S. Ocean Blvd.,Ste. 28
Delray Beach, FL 33483-6583
tel561) 276-4900
fax561) 276-4488
[email protected]
Attorney:lra Abrams, Esq.
 

A:Excel address groups to columns

Assuming that
1) the data is in column A, and
2) there is an empty cell between consecutive records
the code below should work. It will put the transformed data into column C and to the right.
Code:
Sub test()
Dim rng As Range, c As Range, tgt As Range

Set rng = Union(Range("A:A").SpecialCells(xlCellTypeBlanks), Range("A" & Rows.Count).End(xlUp).Offset(1))
For Each c In rng.Cells
On Error GoTo NextCell
Range(c.Offset(-1), c.Offset(-1).End(xlUp)).Copy
Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteAll, Transpose:=True
NextCell:
Next
End Sub
Jimmy
 

https://forums.techguy.org/threads/excel-address-groups-to-columns.992428/
Relevancy 56.33%

I have done a search, and could not find it.

I know there was a post on this earlier but I could not find the post I was looking for.

What I am looking for a macro that will copy specific columns and put them on another worksheet.

i.e.

With Columns between A and AA
Column F,K,M,Q,S,U,Y would all be copied to Sheet 2

Thanx in advance.
 

A:Excel - Copy Specific Columns

Can you not just use the macro recorder to accomplish what you want?

Rollin
 

https://forums.techguy.org/threads/excel-copy-specific-columns.423263/
Relevancy 56.33%

I have thousands of rows of text data in two columns: Column A is City Name and Column B is County Name. I need to highlight all sets of matching data (ie, highlight each row where the city name is also the county name). I have tried using =AND(A$2=B$2) as a conditional formatting rule and also have tried =COUNTIF(A$2:B$2, A$2)=2. The problem is that both of these functions highlight everything that is repeated anywhere in the two columns. I only want a row to be highlighted if it matches discretely within the row (ie: if A2=B2 or A1257=B1257. I don't want it to highlight if A2 happens to have the same value as B1257). Any ideas?

A:Duplicates Across Discrete Columns in Excel

Try this in your Conditional Formatting Rule:
1) Select your Range of Cells, A2 - B1257
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it?s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.

5) Enter the formula: =$A2=$B2

6) Click on the Format button
7) Select the Fill Tab
8) Select a pretty color
9) Click OK
10) Click OK
See how that works for you.MIKEhttp://www.skeptic.com/

http://www.computing.net/answers/office/duplicates-across-discrete-columns-in-excel/20024.html
Relevancy 56.33%

I'm pulling data from another database into excel, I have data coming into one column I want to split into to two columns. The second column of data is dropping the leading zeros can anyone tell me how to retain the leading zeros when using the text to column command?

A:Text to Columns command in Excel

There's a way, but I need to know whether you want the resulting column to be treated as text or number. I suspect text, but can't be sure.

Try this little experiment where I want to split after 123
123456
123056
When you go splitting, select the column after the vertical line (it'll turn black), then click on Text option. You will end up with
123 456
123 056
but the stuff ends up as text, so you can't do math on it without conversion.

An alternative would be to insert a ":" or "x" into the strings, and then split on the separator, then remove the separator. However, if numbers result, as they will, your leading zeros will be dropped unless you apply a custom format.

http://www.bleepingcomputer.com/forums/t/388240/text-to-columns-command-in-excel/
Relevancy 56.33%

I was hoping someone could help me write or find a macro that would take two columns and move it to column A under the previous data and repeat for all columns. Basically user information is located in pairs of columns (typeofvalue, value). So column A and B are for user1, column C and D are user2, column E and F are user3, etc. Bonus if I could have a space in between each once moved.

Current:


HTML Code:
A B C D
User1First User1Last User2First User2Last
User1ValueTypeA1 User1ValueB1 User2ValueTypeA1 User2ValueB1
User1ValueTypeA2 User1ValueB2 User2ValueTypeA1 User2ValueB1
Desired:


HTML Code:
A B
User1First User1Last
User1ValueTypeA1 User1ValueB1
User1ValueTypeA2 User1ValueB2

SPACE/EMPTY ROW

User2First User2Last
User2ValueTypeA1 User2ValueB1
User2ValueTypeA1 User2ValueB1
Each user has 47 rows of "values", for the example I just did two rows worth.

A:Excel Macro to move columns to 1 row after each other.

I did a quick google and found this. It worked for you?

Excel macro to move columns to 1 row after each other

http://www.sevenforums.com/microsoft-office/374441-excel-macro-move-columns-1-row-after-each-other.html
Relevancy 56.33%

I have a huge piece of data in notepad and I want to put it into a spreadsheet, when I copy and paste it puts it all into rows, is there a way to avoid this?
 

Relevancy 56.33%

Adding rows 5 thru 10 on line 11 I use function, and get a total for column A. When I go to column B I have to type in function again - and also in C-D etc. Is there a way to get all the desired columns added on line 11? I use XP home with Excel.
 

A:Solved: Excel-adding columns

Just click on the bottom right of the total in Column A.
You should see a set of crosshairs.
Drag it through the other cells that you wish totalled,
 

https://forums.techguy.org/threads/solved-excel-adding-columns.537292/
Relevancy 56.33%

I have been entering information into a template in Excel. The template was created by someone at work. This template has merged columns. I had no problem entering the information until I added two extra rows within the template. The added two rows are not merged but the merge function is grayed out and I am unable to select it. What could be the problem and how can I correct it?

Thanks,
Tonya
 

A:Solved: Trying to Merge Columns in Excel

can you post an example of the spreadsheet

if you highlight the two cells can you merge
 

https://forums.techguy.org/threads/solved-trying-to-merge-columns-in-excel.460167/
Relevancy 56.33%

I am trying to create a macro in Excel that will sum two different columns of numbers The columns will always 2 Macro Sum 2007 Columns Excel be the same length I am new at quot trying quot to create macros and I can t figure out how to keep the same range to sum just move it columns to the right A sample of the table data is below Thank You Sub Test On Error GoTo CancelExit With Selection Offset Rows Count Columns Count Excel 2007 Macro Sum 2 Columns Cells FormulaR C quot SUM quot amp Address xlR C amp quot quot Offset Rows Count Columns Count Cells FormulaR C quot SUM quot amp Address xlR C amp quot quot End With CancelExit On Error GoTo End Sub I am trying to create a macro in Excel that will sum two different columns of numbers The columns will always be the same length I am new at quot trying quot to create macros and I can t figure out how to keep the same range to sum just move it columns to the right A sample of the table data is below Thank You Sub Test On Error GoTo CancelExit With Selection Offset Rows Count Columns Count Cells FormulaR C quot SUM quot amp Address xlR C amp quot quot Offset Rows Count Columns Count Cells FormulaR C quot SUM quot amp Address xlR C amp quot quot End With CancelExit On Error GoTo End Sub Sale Date Sale Price Adj Sale Price Land Imp Total I would be summing the Adj Sale Price and the Total everytime There is data above and below each quot mini-table quot and all are of different rows I tried to set it up to highlight the first column series as all the rows in that series will alway be the same number of rows All help is greatly appreciated nbsp

Relevancy 56.33%

I have a spreadsheet that I wish to compare 2 columns and bring the information from the 3rd. For example, if A6 is equal to an item in G1:G98, then the number in B6 should equal the corresponding number in H1:h98. The formula I used is =VLOOKUP(A8,$G$1:$H$98,2,FALSE) It works great except the problem I have is sometimes there is no corresponding value in G1:G98. In that case I would like it to enter the number 0.
 

A:Solved: Excel comparing 2 columns

Hi Bsoda,

=IF(ISERROR(VLOOKUP(A8,$G$1:$H$98,2,FALSE)),0,(VLOOKUP(A8,$G$1:$H$98,2,FALSE)))
lol
Hew

(looking at what I just posted, its displaying an extra space in the final FALSE, but its not there when I go to Edit it!!)
 

https://forums.techguy.org/threads/solved-excel-comparing-2-columns.1003722/
Relevancy 56.33%

have designed an invoice form in Excel 2010
have made a Drop down box using the "products" worksheet information in the drop down list. The drop down list shows only column 1.

some ITEMS in the "products" worksheet, have the same name (col 1) with different prices in (col 2)
for example:

the following is an example of the "product" list:
col 1 col 2
apple $44
apple $48
apple $50

I would like to view columns 1 and 2 in the drop down list to make the choice of which price to charge

depending which row "apple" is chosen from, the price goes into the amount column on the invoice, with the description showing only "apple"

I wish to only show "apple" in the invoice description area

so ... how to view 2 columns (in the drop down Box) but only make one entry (from col 1) into the invoice?
 

A:Excel Drop down box how to view 2 columns

It sounds like you would need to set up a third column in your sheet to combine the Col1 and Col2 information and then link your drop down to that. Assuming your drop down tool is an activex or forms type rather than "in cell validation" type, you can then fill your invoice with the information from Col1.
See attached.
 

https://forums.techguy.org/threads/excel-drop-down-box-how-to-view-2-columns.1147281/
Relevancy 56.33%

I have a 3 column Name & Address Excel document and I would like to Print the data as 2-columns on a page. How do I set this up? Thanks
 

A:Print Excel document in two columns

Rafty
When you say 3 columns to 2

Are you asking for help to print out only 2 columns, but not the 3

or are you trying to concatenate 2 of the columns so that when you print you only have 2 columns
 

https://forums.techguy.org/threads/print-excel-document-in-two-columns.548996/
Relevancy 56.33%

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.

www.theofficeexperts.com/downloads/ForJohn1.xls
You can left-click to view it.
Right-click and choose Save target as to save it.
 

https://forums.techguy.org/threads/excel-can-i-put-headings-on-rows-and-columns.127894/
Relevancy 56.33%

Hallo seit kurzer Zeit bewege auch ich mich durch das quot Matlab-Universum quot Demgem freue ich mich ber jeden kleinen Schritt der mir columns specific Read from Excel gelingt Nun habe ich Read from Excel specific columns jedoch eine Aufgabe erhalten welche - so denke ich - mein bisheriges K nnen bei weitem bersteigt Folgender Sachverhalt Mir liegt in K rze eine sehr umfangreiche Excel-Tabelle mehrere Spalten Zeilen vor in welcher nur gewisse Spalten f r weitergehende Berechnungen erforderlich sind S mtliche Spalten sind bspw in Zeile f nf mit einer berschrift versehen Dabei ist nur ein Teil der besagten berschrift jeweils relevant Meine erste Frage ist nun wie ich die berschriften in der Zeile hier bspw f nf pr fen kann Read from Excel specific columns ob etwa neben anderen alpha-numerischen Angaben ein bestimmter Begriff oder mehrere auftauchen Ist dies der Fall soll die entsprechende Spalte in Matlab eingelesen werden M glicherweise gibt es f r diesen Prozess sogar eine quot elegantere quot Darstellung als die aus Excel herausgefilterten und in Matlab integrierten Spalten alle einzeln im quot Workspace quot abzulegen Als absoluter Anf nger bin ich dahingehend nat rlich berfragt und freue mich selbstverst ndlich wenn mein Vorhaben berhaupt auf relativ humane Art und Weise m glich ist Meine zweite Frage betrifft die Vorbereitung und Durchf hrung einer Regression Angenommen ich habe das vorherige Verfahren zwei Mal f r zwei verschiedene Begrifflichkeiten angewendet wie kann ich anschlie end die umfangreichen Angaben zu zwei jeweils unterschiedlichen Variablen nennen wir sie quot x quot und quot y quot so zusammenfassen dass ich eine Regression durchf hren kann Die Regression als solche sollte ich mit meinem bisherigen Wissen realisieren k nnen allerdings kann ich auch dies noch nicht ig garantieren Leider habe ich - gem meiner obigen Fragen - nicht viel was ich an Quellcode anbieten k nnte Somit bedanke ich mich schon einmal herzlich bei allen die sich meiner erbarmen und mich durch das quot Matlab-Dickicht quot geleiten Smile Viele Gr e English Hello for a short time and I move through the quot Matlab-universe quot Accordingly I am happy about every little step that I succeed Now I have however received a task which - I think - exceeds my previous skill far Following facts Mir is shortly before a very large Excel table several rows columns in which only certain columns for further calculations are required All columns are for example Provided in row five with a heading In this case only a part of said heading is each relevant My first question is I like the headings in the row here for example Five may consider whether some among other alpha-numerical information a certain term or more occur If this is the case to the relevant column are read into Matlab There may be for this process even a quot more elegant quot representation as the filtered from Excel and in Matlab integrated columns all individually in the quot Workspace quot store As a complete beginner I am the effect of course stumped and am happy of course if my plan at all in a relatively humane manner is possible My second question relates to the preparation and implementation of a regression Suppose I have the previous procedure applied twice for two different terms how can I then extensive information on each two different variables we call them quot x quot and quot y quot summarized so that I have a regression can perform The regression as such should I be able to realize with my previous knowledge but can I also have not already guarantee Unfortunately I have - according to my questions above - not much that I could offer to source code Thus I already would like to thank all those who have mercy and guide me through the quot Matlab thicket quot Smile Best wishes

http://www.tenforums.com/software-apps/47750-read-excel-specific-columns.html
Relevancy 56.33%

I am a novice at excel I have A 2003 columns Excel Combining in throught A as first names and B through B as last names in a spread sheet I want to make a column of First and Last Names together either by itself like a new column C or Combining columns in Excel 2003 by utilizing either A or B columns I have found the formula A amp quot quot amp B but I don t know what to do with it When I paste that formula into C of a blank column C it gives me first and last name of A and B but how do I get this to replicate itself down the column C to change all seperate first and last names into one cell each containing first and last names Sorry if this is a very basic question but I simply am not that familiar with how excel formulas work yet and how to get them to work over multiple cells Thanks for your help in advance LettuceChat nbsp

A:Combining columns in Excel 2003

Leave the =A1&" "&B1 formula in C1. Place your cursor over the bottom right corner of cell C1. It should change to a thin "+" sign. Press and hold down the left mouse button. Drag the cursor down to cell C226 and release the mouse button. This should copy the same formula structure into each cell in column C while incrementing the row number references.
 

https://forums.techguy.org/threads/combining-columns-in-excel-2003.519782/
Relevancy 56.33%

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.

thanks.