Sheet Preparation By Ordering And Swapping Cells Vba Macro
Jun 5, 2007
in 1 workbook i have 2 sheets (sheet1 and sheet2) with same format of data that I need to compare and compile in sheet3 by a primary key column.
example:
sheet 1 & 2 data format:
|Atr1|Key|Atr2|SubAtr1_1|SubKey1|SubAtr1_2|SubAtr2_1|SubKey2|SubAtr2_2|Atr3|
..................... ......................................../......................................../
......................................Set1.....................................Set2
To be more specific I can compare this with an electrical wiring table, where:
Key is the wire number
Atr1, Atr2, Atr3 are general atributes of the wire number
Set1 are the atributes of the first end of the wire, called "from":
SubKey1 is the "from" device and its subatributes SubAtr1_1 & SubAtr1_2
Similar for the other end of the wire is Set2.
This means that Set1 and Set 2 have similar format & type of data.
I have found on your site an example of macro that is doing the comparision between the 2 sheets of data but I encountered a problem...sometimes on sheet2 Set1 and Set2 are reversed and are highlighted as a difference but I don't want this.
Please help me with macro that would create a rearranged copy of sheet 2 respecting the following conditions:
1. if Key from sheet1 cannot be found on sheet2 then copy entire row on sheet3.
2. if Key from sheet2 cannot be found on sheet1 then copy entire row on sheet3.
3. If Key from sheet1 = Key from sheet2
If SubKey1 from sheet1 = SubKey2 from sheet2
& SubKey2 from sheet1 = SubKey1 from sheet2
Then reverse Set1 with Set2 by swapping cells
and copy entire row (with Set1 and Set2 reversed)to sheet3.
All data on sheet 3 should be sorted by Key.
View 7 Replies
ADVERTISEMENT
Feb 9, 2007
I have the following macro:-
ActiveSheet.Select
ActiveSheet.Copy
Columns("A:D").EntireColumn.AutoFit
Range("A2:B500").Select
Selection.Sort Key1:=Range("B2:B500"), order1:=xlAscending, Key2:=Range("A6:A250"), order2:=xlAscending
Within this I would like to add a couple of features which check:-
1) If there is a number in column A - there should be one in column B
2) The numbers in column B are sequential i.e. 1, 2, 3 etc without any gaps in the sequence
View 9 Replies
View Related
Jul 8, 2014
For my thesis I collected data of football players. I have the data in an excel file, but it is unstructured. Due to the nature of the dataset, I don't want to sort it out by hand. The result should be that the dataset is ready for statistical analysis. I have three categories, namely; Player_Performance, Player_Profile and Transfer_History. I will discuss them now.
Player Performance
In this excel file the performance data of the player is gathered. As you can see, the player ID and the player name are a row above the performance data in different competitions. This data should be on the same row. Player ID, Name and then the performance data. How can I achieve this?
Player Profile
The player profile file is the easiest. I want to delete the double information. Some players are 2 times in the file. I want to delete every row which doesn't start with a player id. I also want to remove player id's which arent followed by player information.
Transfer History
The transfer history is the same as player performance. In the first row you will find the player id and name, and beneath are the different transfers. I want to put every transfer in a row of their own. The row should start with player id, then name and then the transfer. So it is possible that one player id will be on more rows.
In the attachment I put the files as they are and three files of how it should look like.
Player_Performance_SMALL.xlsx
Player_Performance14_EXAMPLE.xlsx
Player_Profile_EXAMPLE.xlsx
Player_Profile_SMALL.xlsx
Transfer_History_EXAMPLE.xlsx
View 1 Replies
View Related
Jul 13, 2013
Does Excel can be used to automate the preparation of the Quad Analysis?
View 2 Replies
View Related
Feb 1, 2010
In trying to better this project i am working on, and getting great help from this forum, I have a new question. Is it possible to swap data in a cell on the fly either through a button, check box or radial?
What I have is a "bid" area on a work book that is supplied info based on the user choice. So what happens is the user goes to a part page, picks and item, and then clicks a button to add the item to a bid sheet. From there the corresponding cells are auto filled via VLOOKUP. The item that is transferred is the part number; also this is what I would like to “swap” on the fly. The part numbers have generic descriptor (which I already have stored on another sheet along with the rest of the data that is auto filled via VLOOKUP) but because the user is transferring that data for VLOOKUP to work I am not sure it can be done.
View 9 Replies
View Related
Mar 21, 2007
I have an worksheet with 10 following columns, the columns may be in whatever order.
Object Attached ToObject Type Date Last ModifiedDescriptionFile IDSizeTypeIs File InUniversal Resource IdentifierVersion Identifier
I am looking for optimal algorithm to swap between columns. I need to get the order like in a list.
View 9 Replies
View Related
Apr 15, 2014
I have SKU numbers in a cell and need to change the location of the sizing digits. Here is the example:
AOP6000SSUMD
AOPSS6000UMD
View 1 Replies
View Related
Apr 15, 2014
I have SKU numbers in a cell and need to change the location of the sizing digits.
Here is the example:
AOP6000SSUMD
AOPSS6000UMD
I am replacing characters within a cell.
View 9 Replies
View Related
Jan 15, 2009
I have attached a 97-2003 .xls file with data for multiple store locations on sheet 1, and the desired result on sheet 2. I am actually using excel 2007, but I dont think I need any special features that it provides.
I will try to explain the issue here without opening the attachment.
Here is an example of the Data on Sheet1
View 13 Replies
View Related
Sep 17, 2009
I need a macro to get the values from cells D29 and H24 in the Resource Calculator sheet and populate it into cells N8 and O8 in the Input form.
Users will then be able to change the information in the calculator and click the macro again to populate N9 and O9 and so on.
Is there a way to do this?
I've attached the file for you to see.
View 13 Replies
View Related
Jun 1, 2009
I have about 45 worksheets in one file. Then all have been named. Is there any quick way to order them? I would prefer not to move each one.
View 2 Replies
View Related
Jul 12, 2009
what i want to do is have a formula set up that works out who has the best position and then re order the number rows to put the player in position 1 at the top. so just moving cells c-n
View 6 Replies
View Related
Sep 4, 2009
I need to ensure that a worksheet is ordered after another in a workbook. The macro that this code is in opens a selected file and carries out various operatins on it, so although I can do this fine when the worksheet needs to be created, there are scenarios where it already exists and I simply need to change the order. The sheet that must come in position 1 does not have a common name and so I cannot reference it via it's name in the code. The other sheet is named when it is created so it doesn't necesarilly have to be in position 2, it's just that the other one needs to be in position 1.
View 7 Replies
View Related
Jan 29, 2010
A number of columns (B to U) contain names, details and scores of players. The A column contains the ranking, e.g. 1 to 100.
If a player (row) is deleted, that ranking number is also gone. Is there a way to make the A column always maintain the integrity and completeness of the numbering, e.g. if rank (row) 16 is deleted, the range of B17:U100 moves one row up and the former 17 is now 16? And if a new row is inserted, the ranking numbers after the insertion move up one row and a new number is added at the end?
2. There are 6 columns of scores, P to U. But out of these 6 scores, only the best 4 count for the ranking. The two worst (highest) scores are dropped. Is there a way to automate the process by selecting only the 4 best (lowest) numbers to be summed into the Total column (C), which in turn determines the rankings in the A column?*
And then can the whole range B2:U100 automatically be sorted according to the ranking in column (A)?
*It is possible that there are 3 or 4 bad scores, all with the same value. In that case only 2 could be discarded, and 1 or 2 would be summed into the ranking cell.
View 8 Replies
View Related
Mar 9, 2008
How do you do a lookup without having to have a list in ascending order?
View 9 Replies
View Related
May 1, 2008
i have a 5 by 10 table (5 columns 10 rows)
values only equal 1 or blank
i want to re-order this table in another identical table so that no row will have more than one value (each row will have a maximum of one "1")
currently, the table looks something like this:
blank blank blank 1 1
blank blank blank blank 1
blank blank blank blank 1
blank blank blank blank blank
blank blank blank blank blank
what i would like the idential table to show is:
blank blank blank 1 blank
blank blank blank blank 1
blank blank blank blank 1
blank blank blank blank 1
blank blank blank blank blank
View 9 Replies
View Related
May 30, 2013
I have 320 rows of data, of which the date is in the first column. I need the data sorted into date order, ie. 04.05.12-->30.04.13 and all the dates in-between (note these are British laid out dates). I am aware, and have already done Format Cells --> Date --> 14.03.01, as this is how my dates are laid out.
I've tried sorting this column, ascending, descending etc etc etc, obviously it needs to be 'expanded' to ensure that the rest of the data in the row follows it to its new position. No joy. It only ever sorts the dates by the day ie. 02.04.13, 04.12.12, 06.06.12. Doing it by the day and not the month and the year also.
I don't want to order 320 data rows myself!
View 3 Replies
View Related
Jun 6, 2009
I have an enquiry where I'm trying to order error messages which occurs in if, ElseIf and Else Statements. The code I have works. However, I would like the error messages to be checked in a particular order. I tried rearranging the conditional statements but had no success. Here is my
View 5 Replies
View Related
Sep 28, 2008
I want to have them in order of shortest price (favourite) first with the relevant horse's name in the adjacent column. I have just discovered vlookup which seemed perfect - until I discovered that when there are two or more identical prices it only returns the 1st horses name.
Is there any way I can use vlookup to do this (with more than one identical value)
View 9 Replies
View Related
Jan 26, 2007
I am trying to come up with a formula which would combine two groups of numbers, put them in numberial order and remove duplicate numbers:
3 6 10 22
1 3 5 9 10 13 19
The above two groups of numbers are in separate cells in two different rows.
the result would also be in a different row: 1 3 5 6 9 10 13 19 22
View 14 Replies
View Related
Jun 7, 2013
I need to make a spreadsheet on excel which shows the production schedule of a product over 4 days.
Then I need to make a supply ordering sheet which shows how much supplies are needed to do the above task and how much each item in the supply list will cost and when the items would need to be ordered.
View 4 Replies
View Related
Jul 15, 2013
setting up a stock ordering system in Excel. I have been sent a spreadsheet with about 400 items I've ordered before and about 15 columns of descriptions which include the name, category of item, manufacturer, cost, number etc.
I would like to set up a simple Excel spreadsheet which staff can use when they want to order some stock, which will automatically fill in the adjacent cells with the product category and unit cost, once the name has been typed into the first cell. Once I add the SUM formula I can therefore have an immediate total of how much each staff member is spending on stock that week, without having to look at the invoice I get back from the supplier.
I know Excel has a predictive text style feature (autocomplete?) but I don't know how to get it to automatically fill several cells in a row with data that is linked to the first cell.
View 14 Replies
View Related
Oct 21, 2013
I would like to reorder a list whilst ignoring any 0 values. I've attached (I hope correctly!) a spreadsheet of what I mean. It's pretty simple, but I've been banging my head over it for a while now!
Sample.xlsx
View 4 Replies
View Related
Feb 14, 2010
1 - We receive an excel file from our customer (approx 20 different customers in total)
2 - I have an excel template we have to use, which will import into our MIS software. This excel template must remain the same or it wont import into our system. ie (Columns must be in this particular order)
3 - Our customer sends their own spreadsheets in their own formats ie, column in a different order to our excel template.
4 - I need to know if there is a quick way to remap customer columns into my excel template
5 - Im looking for a little application that will look at the customer spreadsheet and our template in the same window and let me link which fields need to map where in my template. When I have completed I can save what ive done and run when needed for this customer.
View 9 Replies
View Related
Dec 21, 2013
I have a list on flower names in column A , and the colors in Column B. For sorting purposes I have duplicates in Column A. I am creating an easier way for coworkers to organize the ordering process from wholesalers. On the order sheet It will have the dropdown that lets them choose the flower (ultimately I hope to make this searchable,) Once the flower is selected the next column will allow them to choose from the color available. Attached Is the sheet with my work so far.
View 8 Replies
View Related
Oct 28, 2009
Hi all! I'm hoping someone can help me as I'm trying to complete this for work ASAP. Here's the story. I want to automatically fill out the Monthly Log sheet using the information provided on the "Full Staff" sheet. If E9:E227 in the Staff sheet is listed as "CO", I want to copy the corresponding cells over to the corresponding cells in the Monthly Log Sheet. Also, I want it to automatically paste the information in the next available row (say rows 1-15 are being utilized, I want it to post in 16). Can anyone help?
View 6 Replies
View Related
May 1, 2013
I have an excel sheet that has conditional formatting. Whenever few cells are not in required format, the cell's backcolor will become red. I am trying to design a macro to check if there are any cells in red due to conditional formatting.
View 1 Replies
View Related
Apr 24, 2008
I am trying to write a macro to copy an entire sheet's content into cell A1 for each sheet in a workbook.
I am running into the problem of not being able to copy many cells into one.
Please assist me in figuring out a macro to copy a column or entire sheet to a single cell (preferably iterating through each worksheet in the workbook).
View 9 Replies
View Related
Jun 9, 2006
I browsed through the other posts concerning copying and couldn't find (or maybe I didn't just understand) how to do what I want. I looking for a macro for a comand button that when pressed copies certain cells in the active row to another sheet. I recorded a macro for what I wanted to do ( to make the explenation easier) for one row but even the recorded macro refuses to work.
I'll put the recorded macro here if it helps someone, but as I said even that doen't work
Private Sub Kopiering_Click()
Range("E3").Select
Selection.Copy
Sheets("TOTAL").Select
Range("K3").Select
ActiveSheet.Paste
Sheets("BRL Newbuilding").Select
Range("D3").Select
Application.CutCopyMode = False
Selection.Copy
View 8 Replies
View Related
Jan 24, 2014
I have two sheets and need information from Sheet titled Add-Del_chng positions to be moved to the sheet titled Cost Distribution.The issues here are :
1. The values in Cells A through J should be moved only if col Z value is 'hard coded"
2. The values should be moved to the 13th row.
3. This should happen every time the value is input into col Z of sheet titled add_del_chng.
Currently the macro does not run unless I go into the code and press play. I need this to happen automatically.
View 13 Replies
View Related