I'm quite proficient in Excel but am struggling here, would really appreciate any help
I basically have 80 tabs with IP addresses in Column A and then information in Columns B, C and D related to this.
What I want to do is bring all this together in one sheet
Possible?
Basically I inherited a Excel Workbook which has 128 different tabs, unsurprising this is almost impossible to manage.
My requirement is to merge all of the 128 tabs into 1 tab 'Sheet1'.
The macro should keep going even if there are blanks, it should move onto the next tab only when there are over 10 consecutive blanks in Column B.
I have tried searching the forum but nothing seems to cater for the specifics of this case. I would really appreciate some help. I mean really, i am in a mad panic thinking i will have to do 128 tabs manually otherwise.
I have a file with around 20 tabs. Can I merge all the tabs into just one tab?
View 1 Replies View RelatedI would like to combine 2 tabs into one spreadsheet. I was not able to do so, because it exceeds the max rows allowed in excel. I have installed powerpivots but not sure how to combine data using powerpivots.
View 3 Replies View RelatedI have three source workbooks that needed to be consolidated into a master workbook pasting the data with values & formatting.
The Master workbook also contains 6 additional worksheets that link to the data pulled in from the source files.
My question is how do I write the code so the source files populate the master in a specific order.
For example, the 3 source files are named "Central" "NED" and "WEST" and I need them to populate the master workbook in that order.
This is the code I am using to consolidate the data:
Option Explicit
Sub ConsolidateSheetsFromWorkbooks()
'Author: Jennifer Starr
'Date: 7/12/2013
'Summary: Open all files in a folder and merge data (stacked) on all
[Code] .....
I have been trying to get Excel (2007) to change the colour of tabs to match other tabs in the workbook
Using this code I get the colour code of the current tab
Code:
x = ActiveSheet.Tab.ColorIndex
But when I use this value in a procedure like this:
Code:
ActiveSheet.Tab.ColorIndex = x
The tab is a different colour!
I have a summary page that includes the titles for each tab within the excel 2003 workbook. I want to use the names of the tabs in the summary page and create it into a formula to lookup fixed cells within the various tabs. Sorry for not uploading an excel doc but I was at work earlier and the thread did not load for some reason, so I am reposting it.
View 3 Replies View RelatedMy main data tab is collecting gallons pumped for a particular piece of equipment. I have a drop down box to populate the piece of equipment and VLookup to identify the unit number associated with the equipment. I would like to create a separate tab for each piece of equipment that will track the number of gallons pumped during the calendar year. Here is the format for the main tab:
DateBeginning Meter ReadEnding Meter Read GallonsEquipment IDEquipment Description
1/2/2014565443565625 18212006 ford f250
1/10/2014565625565675 5022006 Chevy 2500
1/11/2014565675565750 754Ford Taurus
1/12/2014565750565830 8012006 ford f250
1/13/2014565830565900 7012006 ford f250
1/14/2014565900566000 10012006 ford f250
1/15/2014566000566125 12512006 ford f250
1/16/2014566125566215 9012006 ford f250
Here is the format for each piece of equipment:
2006 Ford F250
Date Gallons
1/2/14 182
1/12/14 80
1/12/14 80
1/12/14 80
1/13/14 70
1/14/14 100
1/15/14 125
1/16/14 90
The formula I used in Cell A4 is =INDEX(Gasoline!A4:A23,MATCH(1,Gasoline!F4:F19,FALSE),1)
Cells A4 and B4 have the correct values. My problem comes in A5,A6 and B5,B6. These should not have a result because they are different pieces of equipment. Throughout the year, each piece of equipment is going be used on the main tab. How do I keep the individual tabs from picking up the same entry multiple times.
I got the following data and I like the results to be:
Data
Company | Country
ABC | USA
ABC | London
BBB | USA
CCC | USA
Results:
Company | Country
ABC | USA, London
BBB | USA
CCC | USA
Is it possible to this under Pivot? as I need to consolidate lots of data..
I have a large spreadsheet where some data are similar. for Example
Robert $1000
Louis $600
Glen $350
Doreen $2300
Robert $500
Louis $600
Glen $750
Doreen $300
Robert $3000
I want to merge all Robert together, all Louis together and show as one Robert $4500. How can I do that ? Should I use a Look up.
I have 2 data sets which i want to add together. Here is an example.
Data sheet 1
A B C
SAC
L
1
INPUT
L
4
BLIN
L
12
[Code] .......
What i want to do without inserting every line manually.
A B C
SAC
L
1
FIX
L
2
[Code] ........
I have an empoyees first name in A1 (Joe) and Surname in B1 (Bloggs), however, I want "Bloggs, Joe" in C1?
View 2 Replies View RelatedRight now I need help mergng 2 files:
#1 One is a txt file with emails
#2 the other is a .csv excel file which contains several fields: email, name, address, etc
Basically what I need to do is to create another .csv excel file that contains all the emails in file#1 that are also present in the file #2 plus their corresponding additional fields ( name, address ) etc
I extract data from a data base and return the query in details, i need to merge 2 rows of data that has the same project number. example i have project ABC with billable time of 10hrs @rate of £200 and unbill time of 6hrs @rate of £0. The query will split the data as there is a difference in the rate.
I need a formula that recognise that it is the same project and merge the row together!
Need to merge the data on two wrk sheets.data is as given below data in wrk sheet 1:
site_keybrandsiteQ count
BU04191BU4191332
DI00768DI768512
DI01616DI1616480
data in wrk sheet 2:
site_keybrandsiteNQ count
DI00551DI551171
DI00768DI76812
DI00982DI9822
RA07922RA79229
my final report should look like
site_keybrandsiteQ countNQ count
BU04191BU41913320
DI00768DI76851212
DI01616DI16164800
DI00551DI5510171
DI00982DI98202
RA07568RA75681900
RA07922RA792209
I have a set of data which I would like to do some processing on. Basically I am concerned with two columns
Column 12 and Column 9
Column 12 is a unique account Reference and column 9 is an invoice number. My scenario is that 1 account can have multiple invoices. I want to do is serach down column 12 and find all the matches and then I want to take column 9 and combine the data within that with the previous record.
Original Data
Column 9 Column 12
2345 A0001
2312 A0001
2341 A1200
1234 A0001
4569 A1234
3456 A1234
What I want to get to:
Column 9 Column 12
2345,2312,1234 A0001
2341 A1200
4569,3456 A1234
As you can see I basically want to combine any matches with column 9 and seperate with a comma and then delete the record it has taken it from
Function repair_invoice_numbers()
Sheets(target_sheet).Select
rowcn = 2
Do
checknext:........................
I have been using the following code to fill data down a col. For example if a1=blue and a5=green and a7=red. The macro will fill a1(blue) down to a4, then take a5(green) to a6, then a7(red) etc.
Sub Filldown()
If ActiveCell.Text = "" Then
MsgBox "please start with a non-empty cell"
Exit Sub
End If
For Each x In Selection.Cells
If x.Text = "" Then
x.Value = x.Offset(-1, 0).Value
End If
Next x
End Sub
code works great, but I was hoping it could be changed so that instead of filling the data down it would merge the data down. So, in the example, a1:a4 would be merged leaving the word blue, a5:a6 merged, and a7:etc would be merged.
Is this possible?
So I have two worksheets. One sheet has all of the names of attendees to a current event (column a, about 109 entries), and the other sheet has all of the names that have ever registered for my events along with their emails (column a and column b, 3000 entries). I need to merge data from column B of the second sheet to the corresponding name in the first worksheet, and disregard the extras (or just put them at the end so i can clear the contents). So I am just trying to get the email addresses of the people in the first sheet, from the data in the second sheet.
I've tried to look at the consolidate data function, but that does not match up the names to the email addresses correctly. What about a filter?
I have excel data containing in column A "Plot number" which is repeated and other four column is land area and the next column is "Land Owner Name". Now I want to merge same plot no if the land area is same if differ then I want to leave as it is. And another thing I want to do that the all land owner name is merge in a row.
View 8 Replies View RelatedSo I have several columns of data that have a location and then some numbers after it in additional columns. I need to be able to sort it so that all locations that are in both location columns are sorted first, and then any locations that are in one column but not the other column follows. Also the data associated with each column that follows the location needs to remain next to the location. Many times the columns will be of highly disportionate lengths too.
I've attached an HTML table so you can get an idea of what I am looking at, except what I'm dealing with is like.... hundreds of cells long.
I want to be able to turn this:
HTML Code:Â
<table border="1" bordercolor="#FFCC00" style="background-color:#FFFFCC" width="100%" cellpadding="3" cellspacing="3">
<tr>
<td>A</td>
[Code].....
And in this 6 row example, I'd actually only be interested in the 4 letters that were in both columns. If it makes it easier, I would be okay with discarding the last rows, but would rather just have them sorted at the end, so I could check if....for example there were two different spellings of the same thing, and they should actually be included.
Basically, I need to merge the values in row A into specific rows in column D (as indicated by A1, A2, A3 etc).
View 6 Replies View RelatedI was given 6 spreadsheets combined into one (the reason is immaterial-but valid) it is what it is... unfortunately! (42 columns, 14,000 rows of which 450 names are duplicated as illustrated below)
I want to merge duplicate data.. but not the way I see it shown in all the 'merge' and the 'delete duplicate' threads I have seen so far.
I manage a homeless shelter and someone duplicated the spreadsheets and started using the duplicates to enter fresh data.... several times!
As a result I now have somewhere around 8 instances, (or 7,5,3 etc) of every homeless guest, but it has duplicate data in some columns, and new data in others, for instance:
I want to turn this....
Name SS# 1st entry date, 2nd entry date, 3rd entry date, fourth entry date, etc...
John Smith, 123-45-6789, 1/5/2010, 7/13/2010, 3/30/2011, 5/16/2012
John Smith, 123-45-6789, 1/5/2010,
John Smith, 123-45-6789, 1/5/2010, 7/13/2010, 3/30/2011,
I tried de-duping based on duplicate rows and that worked with the pure duplicates get out of the mix, but if I try to to de-dup on only the first two columns of data it may remove date I want to keep.So I need to de-dupe and merge at the same time.
I have 3 files out of which Inv.txt is the main file.
I would like that the other two files get opened and the the number in column 3 gets vlookup in the file 20140701 Inv.txt and it adds the data from 20140701 INv.txt column 7 to the last column in INv.txt.
I would like that the other two files get opened and the the number in column 3 gets vlookup in the file 20140701 Inv.txt and it adds the data from 20140701 INv.txt column 7 to the last column in INv.txt.
I would like that the other two files get opened and the the number in column 3 gets vlookup in the file 20140617 Inv.txt and it adds the data from 20140617 INv.txt column 7 to the last column in INv.txt.
I would like that the other two files get opened and the the number in column 3 gets vlookup in the file 20140617 Inv.txt and it adds the data from 20140617 INv.txt column 7 to the last column in INv.txt.
The two file names that it looks up from are always with a date stamp (Current date-6 Days) so this needs to be dynamic.once it has all the data in Inv.txt just save the file.
I have a new project that needs macro code. Your help is very much appreciated. We have a spreadsheet with duplicate accounts meaning two or three rows with the same account but different information. We want to use only one row for one account and move the new data from the same account to one row only to the right and delete the duplicates. Can someone please help me with this?I read so many post and I tried some of them but it only delete the duplicate row and not copying the new data from that row to one row only. Also, the other code I tried was retaining only the current or old data. Actually, to elaborate more, I want to get the new data in each cell of the same account in multiple row and move it in one row to the right only and delete the duplicate in that same account.
View 14 Replies View RelatedI'm sure this sort of thing has been discussed so if appropriate please point me to a relevant tip or topic but I am going to put my case clearly as I really need to get some data formatting for a business card data merge with InDesign.
I have to ensure that all the data is formatted uniformly across the business and this includes separating all the numbers in the business cards with a single space and finally including the p/f/m identifier and outputting as csv...
The spreadsheet looks like this ....
i had different sheets containing the same type of data in every sheet.what i want is to merge all data into one sheet.i had attached the sample sheet for reference.also let me know how to know the last cell in the data sheet
View 3 Replies View RelatedI've got two columns:
A, B
abc, def
qwe, rty
asd, fgh
zxc, vbn
And I need to bring these together in one column so it looks like this
C
abcdef
qwerty
asdfgh
zxcvbn
I use the following code to merge all data a "master sheet", but I want to take the data and put it next to each other on the "master sheet", not under. For example, my range of data is "A1:D15" and I want to copy from all sheets. So my first paste would be A1:D15, then the next should be E1:H15.
I would like the code to find the used range, like it does currently.
Code:
Sub Merge()
Dim ws As Worksheet
ActiveSheet.UsedRange.Offset(0).Clear
[Code]....
I've found macro, which merge rows with the same data
Example:
BEFORE:
january
1st
january
2nd
[Code] ........
Macro:
Option Explicit
Sub MergeSame()
Dim r As Range, c As Range
Dim i As Long, j As Long
Set r = Range("a1", Cells(Rows.Count, "a").End(xlUp))
[Code] .....
But i need use the same procedure for columns, it means:
BEFORE
A
B
C
D
E
F
january
january
january
february
february
february
AFTER
A
B
C
D
E
F
january
february
I've tried modified macro marked above, but without success...
I have 447 worksheets in my workbook, I need to consolidate them into one sheet and transpose the data so that it goes into across rather than down. I really only need data in rows 19 to 40 but the data in 29, 30, 31 A to I needs to be in one cell.
View 5 Replies View Related