RCA Page for forum.xlsx
I'm trying to get entries from one table to appear in another. The first table is like this:
DateDate
Customer Column1
Description Column1
Customer Column2
Customer Description2
Created By
The table I want to put the data into looks like this:
Date
Customer
Description
Created By
Our company gets excel spreedsheets with UPC numbers. The numbers are divided in parts or columns. Sometimes it's in 2 columns; sometimes in 3; and sometimes in 4 like this:
A_____B_____C____D
023__14444__779__9
023__14442__789__7
I'd like to be able to concatenate the digits and have the entire UPC number appear in a blank column to the immediate right and go straight down the line and calculate them all. I figured out how to do this with separate macros for 2, 3, and 4 digits or columns.
Sub Combine_2_Part_UPC()
Do While ActiveCell ""
ActiveCell.Offset(0, 1).FormulaR1C1 = ActiveCell.Offset(0, -1) & ActiveCell.Offset(0, 0)
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Is there a way I could do this with just one macro, by maybe selecting the digits of the first UPC number in the first row of the data to cue the macro in as to how many columns to process?
(The UPCs are in the midst of a lol of other data like product cost, so a macro probably wouldn't be able to figure out how many to calculate on its own.)
This is my formula with concatenation: =K3/K1*100 & "c" & " Each"
This is the result of the values in K3 divided by K1: 6.41666666666667c Each
I would like to limit the decimal places to two: 6.41c Each.
I have tried to format the cell and none of the formatting options have any effect.
If possible I would like to use the rounding function but I do not know how to combine the formula + text + rounding.
[URL]...I was trying to use this thread's solution macro on my sheet that is only consolidating columns A, B, and C with a couple thousand lines and I've had it running for over an hour now. There hasn't been any errors but is there any particular reason that might be causing it to take longer than it should aside from having to go through a few thousand rows?
Basically, I only want 1 Version of data in Column B and any other rows that has a match in column B, i want to consolidate unique data from Columns A and C.
A B C
AB 123 AAC
BA 131 AAC
AB 222 CAA
Res:
A B C
AB 123, 222 AAC, CAA
BA 131 AAC
I have a spreadsheet in Excel 7 for a lifelong cashflow model. The main worksheet is called 'Projection' with ages in rows & income (from a variety of sources) and expenditure (of various kinds) in columns fed with data from the Input sheet.
On the 'Input' sheet I have a button called Format Graphs which controls the following macro:
This isn't absolutely necessary, but being able to do this easily would cut down on a lot of tedious jobs for me. Let's say I have a set of data like this (I tried to space it out as best I could to give the idea):
View 3 Replies View RelatedI have a large data set that I want to pivot on to produce various results. I can easily pivot on two columns to produce those Unique IDs where East AND West are "Yes" or "No." I want an easy to filter on results where either East OR West is "yes." I could do this with a joined (Concatenated column) such as East/West but was hoping there might be an easier solution built into Excel pivot feature.
Unique ID
East
West
1
Yes
No
2
Yes
Yes
3
No
Yes
4
Yes
No
5
No
No
slider.JPG
What is this and how do I create it?
is it possible to create or customize a worksheet with 30 columns and 120 rows only.
View 4 Replies View RelatedSo I'm trying my hand at creating VBA buttons and functions that do the following.
Button 1 :
to add a column with every click. The button will create one extra column. So by design there will be at least 1 column. Example if the button is clicked twice it will create 3 columns. I just need a buffer col. (contains no data)
Button 2 :
to delete empty columns leaving only one empty buffer column. That is this button will purge empty columns except the buffer column. OR if ALL the columns are filled, Button2 will create the buffer column.
Eg. I click Button 1 three times. It creates four columns. I only fill two columns. When Button 2 is clicked it will delete all the empty colums except the buffer column. OR I fill in all four columns, there is no buffer column, when I click Button2 it will insert the buffer col.
Button 3 :
to add a row with every click. The button will create one extra row. So by design there will be at least 1 row. Example if the button is clicked twice it will create 3 rows. I just need a buffer row. (contains no data)
Button 4 :
to delete empty row leaving only one empty buffer row. That is this button will purge empty row except the buffer row. OR if ALL the columns are filled, Button4 will create the buffer column.
Eg. I click Button 3 three times. It creates four rows. I only fill two rows. When Button 4 is clicked it will delete all the empty rows except the buffer rows. OR I fill in all four rows, there is no buffer rows, when I click Button4 it will insert the buffer row.
In the sample file there are six columns filled with data. The 7th col is empty(the buffer column). The column after that calculates the average of data in filled columns. There are eight rows with data and the ninth row is the empty buffer row.
The "add" buttons must be able copy the format/formula of the column/rows BEFORE the buffer colums/rows and insert it before the buffer column/row.
I need to make named ranges from an unknown number of columns(at least 1) each with an unknown number of rows. Each column has the name of the named range as the first row, and then a variable number of rows containing part numbers.
I can do it 1 by 1, but id rather do it in a loop so that blanks dont cause errors. there will be different people using versions of this sheet with different model/part number information What i've tried: Count number of colums with row 1 containing data (11 max, which is more than will ever be used) add into array(I know i dont really need to add into the array, but i might use it later for some other code). The problem i'm having is finding the range of rows that need added to the named dynamic range and adding it.
modelcount = Range("G7") 'G7 (for now) contains =COUNTA(H1,I1,J1,etc)
For i = 1 To modelcount
Redim Preserve Models(0 To i)
Models(i) = Cells(1, i + 7)
Range1 = Cells(2, i + 7).Address(xlA1)
lastRow = Cells(rows.Count, i + 7).End(xlUp).Row
Range2 = Cells(lastRow, i + 7).Address(xlA1)
Reference = Cells(2, i + 7).Address(xlA1)
ThisWorkbook.Names.Add Name:=Models(i), _
RefersTo:="=OFFSET(Reference,0,0,counta(Range1:Range2),1)", Visible:=True
Next i
This gets me the range i need, but doesnt create the named range properly. If i go to insert>names>define, the named ranges are created, but they dont relate to the data in any columns. It shows the variable names rather than the cell range the variable represents.
I am trying to create a straight column list that can take the rows and columns of a table, and list only the nonblank items. The formula I am using only seems to work with one column, not multiple.
Formula:
[Code] .....
In A1
01234
In B1:k1
01 02 03 04 12 13 14 23 24 34
I am looking for a formula that will combine each digit together as a 2 digit value
Is this even posible?
The value in A1 could range from a 3 to 9 digit value.
I have a game where people are scored on the spreadsheet, but they can join anytime, so everyday, I get a combined list of points. The list consists of people who joined previously, and the new players. How should I combine the points?
[URL] ..........
Trying to combine Column A (First Name) with Column B (Last Name) Probably very simple.!
View 2 Replies View RelatedI have monthly reports that come to us from a Data Warehouse. They vary in size(row length). The problem is that the information in one column actually belongs in the next column (i.e. C to D). As this information in Column C is just an "offset" from Column D there is no issue with overwriting any data in column D. Manually, I could copy the columm and Paste Special, Skip Blanks, to column D.
I've been trying to do this in code as some reports can be fairly lengthy: As there are some blank rows, I've inserted this FinalRow procedure, to provide the size of each report:
FinalRow = ActiveSheet.UsedRange.Rows.Count + _
ActiveSheet.UsedRange.Rows(1).Row - 1
However, in trying to use the FinalRow value in a "Copy the contents from Column C....Cells(20,3 to FinalRow, 3) and then PasteSpecial to Column D (Operation:=xlNone, SkipBlanks:=True) has not been productive. It keeps producing a number of Errors, in every way that I have tried to make this work.
If I have two columns which are lists of names, how do I make a new column which merges the names from each column into one list, and will refresh when new data is added to the two originals.
View 14 Replies View Relatedive got columns of dates that i want to aggregate up into a single column of distinct dates. the dates columns are of varying length, some with gaps in the middle of the column, and some columns are blank. dates in teh columns may duplicate, but i only want distinct dates copied in the single column.
i cant do this by hand because there are dates in around 200 columns so i need a macro or way to do this quickly. ive attached a picture to explain the problem.
I'm working on a project that spans three sheets. A2:A151 of Sheet 1 and 2 is a list of names. The names may repeat and they probably won't fill all 150 rows. What I'd like to do with formulas is combine Sheet 1 and 2 into a single list in Column A of Sheet 3 without repeating names or showing blanks. I'm fine with multiple formulas and hiding columns, whatever needs to be done to make it work. Is this possible?
View 2 Replies View Relatedsee the picture below to get an idea of what i want to do.
http://aycu15.webshots.com/image/362...0389296_rs.jpg
I'm trying to write some code to Hide columns if the first 3 characters of cells in a range equal the contents of another (formula equivalent would be something like this =IF((LEFT(A2,3))=A1,"HIDE COLUMN", "SHOW COLUMN"). I have gotten this far but cant get it to work;-
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, cell As Range
On Error GoTo ErrHandler
Set r = Me.Range("B7:CG7")
Application.ScreenUpdating = False
Application.EnableEvents = False
Row = 1
col = 1
For Each cell In r
If cell.Value = "" And Left(cell.Value, 3) = cell(Row, col).Value Then
cell.EntireColumn.Hidden = True
Else
cell.EntireColumn.Hidden = False
End If
Next
ErrHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
I have a big amount of data and want to consolidate it.
I have identical text in Columns 1,3 and 4, but the text in Column 2 is different for all.
How do I make 1 row instead of 3 with Column 2 consolidated?
See the attachment : Excel_list.xlsx
I've attached a spreadsheet with a simplified example of my situation. Notes appear at the far right to explain my desired result. I've read through lots of posts on the forum but haven't yet found anything to mimic.
I'm building a calendar to display promotion activity by week for multiple customers and multiple products. The source data only lists one event per row, so the current result is not as concise I know it could be. I end up with twleve different rows for customers with activity each month on the same product, where I'd like to see just one row with all activity for the year.
Can anyone assist with either VBA or a formulaic solution? I've tried a few things with if/and statements followed by a concatenation of previous rows, but this seems like it will not have the intelligence to calculate only rows that match, I keep coming up short. Can OFFEST be used in some way?
Date formatting. I have two columns first column is for the date (3/6/2013) and the second column is for the time (12:37:16 AM). I would like to combine the content of both columns to be like (2/11/2013 12:35:00 AM) .
I am using concatenate but I don't have any luck. How to combine the content of my two columns.
I am waaaaay in over my head here. I've not done any scripting/macro/vb/whatnot work in Excel, but I'm staring down a potential all-night project manually merging data.
Here's what I have:
I have 2 spreadsheets with a total of 100,000 rows of item descriptions. The problem is that I have to combine the text in the rows to create a complete item description into one cell. The number of rows for each description varies from 1 row to 7 rows. Excel experts here are using the term "concatinate."
There is one common element to the item description. There is a unique 11 digit code (in its own separate column) in the appropriate number of rows for each description. Note that the unique # I identified below doesn't look right since this document does not allow me to show the numbers in a column.
The spreadsheet looks like the following (I have created 2 item descriptions). I have at least 40,000 more.
PAD, EASEL, 27" X 34", ERASABLE, STATIC 61562030632
IMAGES, 34 SHEETS/PAD 61562030632
AVERY #24-391 OR EQUAL 61562030632
PAD, WRITING, 8.5 X 11.75, LEGAL RULE, 61562030940
PERFORATED 61562030940
AMPAD 20-___ 61562030940
RMOD FOR COLOR AND TO COMPLETE CATALOG 61562030940
NUMBER 61562030940
I could use a formula or a macro that would combine the descriptions into one cell. One other problem was identified as I tried to combine the text. There has to be a space created after the last letter of each row, otherwise, the last word in the row combines with the first word of the next row.
I have a list of data elements listed row by row with headers
last 4 of SSNFull Nameschool_codeterm_beginterm_endSchool Rep NameEmail
1111 Jim smith 00104600082012 082016 school reptestemail@hotmail.com
2222 Jane Doe 00104600082012 082016 school reptestemail@hotmail.com
3333 justin justin00104600082012 082014 school reptestemail@hotmail.com
4444 Joe Joe 00104600082012 082016 school reptestemail@hotmail.com
5555 Jake Jake 00104600082012 082016 school reptestemail@hotmail.com
What I need to do is to get all 5 rows into one row and duplicate the headers so row 1 has all fire rows of information one column after the next comlumn. If I can't duplicate the headers easily and can just aggregate the data into one row in cell by cell that fine.
Sample document attached for review
I have received a 'data dump' from a local county assessor. It is formatted for their use in displaying on their website. This particular file associates a parcel number with the legal description. However, with longer legal descriptions, they have replicated the parcel number and continued the description. The fields in the sample I have attached include (A) as the parcel number (which is common through all the files), (B) a county id, (C) the order that this specific portion of the legal description shoud be, and (D) the legal description, itself. In the sample you will see one specific parcel number that repeats itself more than 20 times, while other parcel numbers only appear once. What I hope to do is have one row for each parcel number with no replications. I hope for (A) be the parcel number, (B) be the county id, and (C) be the combined legal description. There should be approximately 275,000 unique parcel numbers.
A second option would be to move the lower continuations up, into the same row. For the example above refering to the 20 rows of legal description, I can work with 1 row and 20 columns(E through Z).
The provided coding was perfect, my problems were found to be the environment I was working. Once the data was prepared properly, the coding worked very well.
Not really sure what I want to accomplish is called but here's a description:
I've got a file with 2 columns
Column A Column B
C53445002558-001--104
C53445540.00
C53455002558-003--105
C53455540.00
C53465540.00
C53475002558-003--105
C53475760.00
C53485002552-002
C53485002558-003--105
C53487294.00
C53495002558-003--105
C53495540.00
What I want is the following:
C53445002558-001--1045540.00 ....
I have two spreadsheets of 27,000+ rows each.
Each one consists of user data for each time they logged onto our systems. The problem is that there is one row for each login. There are anywhere from 1 to 20 (or so) rows per user. I need to turn that into a spreadsheet with one row for each user.
In theory, the problem shouldn't be that difficult. Each user has a unique user ID, and the only math that's required in the merge is a simple adding of numbers from three columns to give a single total for each user.
So far, the only workaround I've been able to come up with is using the subtotal function based on the unique user ID which will at least isolate each user and total up their usage statistics. But it still leaves me with the problem of deleting the now uneccessary extra rows by hand, and then pastin the data back into a new sheet. Using vlookup, I think.