I have 2 columns of Data that contain address fields.
SHEET1
Address1 Address2
%MW4003 %MW4004
%MW5643
%MW9878 %MW9879
%MW13002
%MW5643
etc
The address2 Column is based on a formula.
What i need to do is combine the 2 columns together in another sheet in a column. I do require duplicates to come through, and ideally i need it sorted.
SHEET2
Address
%MW4003
%MW5643
%MW5643
%MW9878
%MW9879
%MW13002
Imagine I have some columns of data. How can I stack multiple columns on below the other in column A?
For example, A column is blank now. B,C,D have some data.
A
B
C
D
1
[Code]...
Can all the columns be stacked in the empty column A?
A
1
ab
2
cd
[Code]...
This is just an example. I have 8000 columns in reality.
Can someone pls. assist me with obtaining an Excel or VBA solution for joining data from across a range of cells/columns into one cell? The single cell containing the intended output has to include the name of the source(s) that are noted on the header column and the price corresponding to each source but would exclude the sources which did not have pricing data. Example of logic is noted below:
Cells B1 - D1 contains names of pricing sources.
Cells B2 - D2 contain prices from these pricing sources.
Intended output: Cell E2 would have the names of all the pricing sources that had prices as well as their corresponding prices; but would exclude those that didn't.
Lastly, pls. see attached Excel file for format of data and intended output in Column E.
I have two spreadsheets, A & B. A can be thought of the master spreadsheet and has one record for every employee giving personal information, including a personel number. B has 0, 1, or 1+ records for every employee in A. Each of these records has a personel number. I want to copy the value of a specific field from B to A.
For example:
A:
personel number1
personel number2
personel number3
B:
personel number1, 'sally'
personel number1, 'john'
personel number3, 'pete'
merge A & B:
personel number1, 'sally', 'john'
personel number2
personel number3, 'pete'
So I have two sets of data, I've attached a worksheet which has them both. The datasets have different intervals, but I need to drag "Colour" from Data2 into Data1 and associate it with the correct interval. Sometimes the intervals match up and sometimes they don't - which could be a problem, but as long as its within a small spread it's fine. The "Colour" column in Data1 is filled with my desired result, the source being Data2.
ManyToMany_Example.xlsx
I have a list of numbers from cells A1 to A150 (they are five digits long), I need to join them together preferably with a the following format '12345', I have tried concatenate but it would take too long to type it all out.
View 2 Replies View RelatedI have same tables(same name of columns in all tables) with different data in 30 different workbooks...can I somehow merge data from all tables in one table?
View 2 Replies View RelatedBelow is the data layout. I want to join values in value column if Condition is true. SO, for below situation answer should come ACE.
TRUE
A
FALSE
B
TRUE
C
FALSE
D
True
E
I have to cell values that are strings and I want to add the two string values together to form a sentance, when I do this using a + sign or a & sign there is no space between the 2 strings, how do I get a space?
View 3 Replies View RelatedI am prepare budget salary base on actual joining date and budgeted joining date. I can only manage to get the formula by joining month not by joining date
View 10 Replies View Relatedi have two entries in my excel sheet which i want to get noted in the other cell as follow
A B
1 ORDER NO. 480
2 GOODS 5
3 P.M 480X5
as my table have data in B1 AND B2 so i want B3 automatically becomes 480X5 so how can i do it
I have the following three codes repeated in five different User Forms,
Is there a way to put them all in one independent module?
I feel like I should be be able to do something like Join(Range(XX)," ") to create a space-separated string of values...
Have I missed an easy one-liner, versus one by one concatenating the value from each cell?
In this case, I am doing exactly that - taking 4 columns of more or less unimportant data and cramming them into a single column just in case they are needed someday.
Sub Test()
Dim strTemp As String
strTemp = _
Range("C2").Value & " " & _
Range("D2").Value & " " & _
Range("E2").Value & " " & _
Range("F2").Value
'//Result: strTemp = "a b c d"
'//This fails - is it possible to do something like this
strTemp = Join(Range("C2:F2"), " ")
End Sub
I read several forum questions for data join and merge but not found a solution for the following scenario. Is possible do it ALL in one macro? I have two different xls files.
file1: sheet has the following structure
refcode price count
XXBK443 200.0 3
KKKN339 333.0 2
etc..
file2:sheet1 has the structure
refcode prod_name color height
XXBK443 prodname1 green 10
ZZZZ000 prodname2 yellow 22
KKKN339 prodname3 white 15
AAAA111 prodname4 white 30
etc..................................
Here is my data:
Column A: Entries
Column B: New Entry
Column C: Total New Entries
Version:1.0 StartHTML:0000000105 EndHTML:0000004087
StartFragment:0000001523 EndFragment:0000004041
****** http-equiv="Content-Type" content="text/html; charset=utf-8">
****** name="ProgId" content="Excel.Sheet"> ******
[Code] .......
Formula in B2: B17
=IF(RIGHT(A2,10)="_New Entry",1,"")
Formula in C1
=SUM(B2:B17)
I would like to remove Column B, and add the formula in C1 itself. Column B is not of any use, it is just showing which is a new entry and C1 is totaling all the New entries, which is in this case is 4.
I need a formula something like this:
=SUMIF(B2:B17,IF(RIGHT(B2:B17,10)="_New Entry",1,"")))
I have database of my entire staff with thr date of birth and joining date, in the format "mm/dd/yyyyy".
I want if the date of birth is equal to today()+1, that means if someone is having his b'day on 25may ,and today is 24 may so its cell colour should turn into red.
I know conditional formatting is to be used here. but bit confused with the formula.
I am trying with this
=IF(TEXT(C3,"dd-mmmm")=TODAY()+1,true,FALSE), but not working.
means I want if someones b'day is coming , I want to ge t informed earlier.
I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3.
ID 1ID 2
12342122
45671112
89101718
11122678
13144544
15162324
17189987
19201215
21221928
1976
2576
2345
4678
In my actual project I'm comparing 2 columns in the same worksheet. The column are column B with data in cells B2:B10266 against column C with data in cells C2:C18560.
I have a workbook that has worksheeets for every day of the month. The data in the worksheet consists of columns (employee #, name, clock in/out times, and break penalty).
What I am trying to do is create another worksheet that searches all the other worksheets for a "yes" in the "break penalty" column and then create a list of all the employees that received a break penalty for the entire month. I would like this to be able to auto populate throughout the month as data is entered and not have to use a filter every time I want to compile this list.
I would like to take the data from worksheet1 and put into worksheet2 but limit the length of a list (the real spread sheet has over 100 rows and i would like them in 4 sets of 25 versus the example I provided). Is there an array or macro that would make this work (keeping the formatting)..
Excel 2007
B
C
3
Name
Company
[Code].....
I have a need to combine multiple columns into a long list of results in 2 final columns. Here is my example...
Current layout with varying number of data points per row...
ColumnA | ColumnB | ColumnC | ColumnD | etc...
example1 | Test1 | Test2 | Test3 | Test4
example2 | Option1 | Option2
example3 | Number1 | Number2 | Number3 | Number4 | Number5
Desired Result
ColumnA | ColumnB
example1 | Test1
example1 | Test2
example1 | Test3
example1 | Test4
example2 | Option1
example2 | Option2
example3 | Number1
example3 | Number2
and so on and on and on until all rows have been filled in down the file...
Is this possible?
how to set one entire columns text to two different colors based on another columns values. So for example I have column A and B. Column A has two values called Internal and External. Column B is a title table so the entire column is just titles. We'll say it goes for 20 rows if you need a row count. What I am looking to do change the text in Column B to Red for External and Blue for Internal. I tried the conditional formatting and I just can't seem to find the right option.
I'm using Win 8.1, Office 2013.
when i copy columns resulted from another columns operations and paste in new sheet i got garbage ,could you tell me why and how to overcome this problem.
View 2 Replies View RelatedI have four columns of data, as follows:
label 1, value 1, label 2, value 2
I need to create a formula in the fith column that for each line will tell excel to:
look for entry in 'label 1' in 'label 2' if there is a match, then subtract value 1 from value 2, display result.
I have tried doing this with SUMIF but am getting nowhere fast....
I have been researching this for 3 days and I cannot find a solution. I have City, State information in A1. I also have City, State information in B1. I need to put them into City (D1) and State (E1).
View 4 Replies View RelatedIt's quite easy, I am trying to do a droplist (that's easy) but I want that drop list to take infos on 2 different columns and fill the 2 columns I want in the main sheet (see attachment)
On tab 'dealers' I have dealers name and account numbers. On tab 'Mileage' I have a cell call "NAME' and a cell call 'Accnt #/NB'. Here is what I want to do:
On sheet 'Mileage' I want to choose from a drop list a dealer name and I want Excel to populate the cell "Accnt#/NB" automatically by chosing the account number that is appropriate to the dealer choosen which is column 'B' in the dealers tab.
what I'm after is a macro to check the contents of Column 'A' against column 'B' and display any duplicates in Columns 'C' & 'D'.
N.B. The headings of Columns C & D are :-
C = Value Found in Column A
D = Value Found in Column B
Any duplicate entries logged in columns C & D should be listed in C2,C3,C4....C20 and D2,D3,D4......D20 etc (in effect creating two new lists)
I have file with so many columns and i want to keep only columns i want.
Data
genredyellowgreenwhiteblue
1aaggttccbb
2aaggttccbb
3aaggttccbb
4aaggttccbb
5aaggttccbb
expected
genredwhite
1aacc
2aacc
3aacc
4aacc
5aacc
for example here i want to keep only gen, red and white columns only out of columns what i have in my data. I have so many columns in my original data but here i given just small example. How to proceed with macro or any other way because removing manually taking long time for me.
I have a spreadsheet with 10 columns which are for tags for products. Some columns are filled in for 10 products and some are not. For instance, product 1 has 7 tags while product 2 has only 2 tags and so on. Now what I am looking to do is combine all the tags for each product with a "," inbetween each one. The problem is if I just use a simple =A2&","&B2&","&C2 etc formula is that if there are only 2 tags then I get 8 ","s. I also tried it with an IF statement but by the time I was done nesting items I became cross eyed and said there has to be an easier way to do this.
I have create a spreadsheet with the data and the result that I typed into the "result" column.
I have a worksheet with 20+ columns. For this macro, I only need to focus on 4 of them. However, none of these columns are ever in a fixed position so the macro would need to find them by name and NOT by column position. Here they are...
1. Vacation Type (will only have a text value of either "Cold" or "Warm")
2. Vacation Started (will always have a date *x/xx/xxxx)
3. Vacation Ended (sometimes it will have a date '*x/xx/xxxx' and sometimes it will NOT have a date and will be truly blank)
4. Number of Days (currently has ALL truly blank cells)
THIS WHOLE MACRO SHOULD NOT BE CASE SENSITIVE ANYWHERE
Here's what I would like the macro to do...
Scenario 1 - for "Cold" values Find "Cold" text values in the "Vacation Type" column
"Cold" values WITH a date in the "Vacation Ended" columnIF there IS a date in the "Vacation Ended" column in the same row, put the number of days difference between the "Vacation Started" column and "Vacation Ended" column in the "Number of Days" column.
The amount of days in the "Number of Days" column will determine whether these cells should be highlighted GREY or RED.
Scenario 1-AIF the number of days difference is 7 days or less, highlight the cells in the "Vacation Ended" column and "Number of Days" column RED.
OR…
Scenario 1-BIF the number of days difference is MORE than 7 days, highlight the cells in the "Vacation Ended" column and "Number of Days" column GREY..................................
I've got some time values in an Excel Sheet in the format hh:mm:ss. I need to split them into columns (including the colon) like below:
hh: | mm: | ss
I can do this manually using text to columns but when I use text to columns in my macro, it automatically changes the time format to h:mm:ss PM