In Column A, anytime a specific "text" appears, I want to concatenate the information in the row, and place it into worksheet Data-list under a specific heading.
[URL]
The formula I would use (not vba) is =IF(A3="FN",B3,""), then I just copy it all the way down. However there are 27K cells, which is why I want a VBA Code
I need a formula to drag down the attached that will place zeros infront of any numbers that don't have 9 digits. so if a part number consists of 6 digits then i need 3 zeros in front of it, if a part number consists of 9 digits then i dont need any zeros proceeding it.
View 2 Replies View RelatedI want to search upwards in a column to find the last value not equal to a value (0,"", etc) so that I can perform a calculation using that value. Here is an example of the sheet.
A B
1 2121
2 1738
3 2159
4 2180
5 0
6 484
7 484
8 0
9 484
working my way down the column, what I want to do is take a value in column B and add it to the value in column A if the value in A is not 0. The first value in B will always equal the value in A if that value is not 0. If the search upwards shows only null fields or it hits the first row in the column without finding a value, that value in the current B cell should show the value in column A. I want the field to show null if A is 0. I want a formula that will look upwards in the column to find the last non-null value to add my value in column A. example : formula in B5 should show a null (""), the formula in B6 should show 84 (B4+A6), the formula in B7 should show 88 (B6+A7), formula in B8 should show null (""), etc. Column A will change values from time to time and I would like the formula to automatically be able to handle the change in values.
i will illustrate what i want to do here with these two excel sheets below. I want to some sort of coding that searches for A and B (Name of the Branch) from sheet 2 and copy the No. of transactions given against A & B from sheet 2 to sheet 1.
OR keep just A & B in sheet 2 and delete the rest of the rows so that i can manually copy and paste values against A & B.
SHEET 1
Name of the Branch
No. of Transactions
A
B
[Code]....
I'm using a userform to report on set of worksheets. Two of the functions are:
- reporting on the number of contacts with a client
- displaying the details of all contacts with selected client in one textbox.
I can't work out how to concatenate the text of all the contacts relating to one client in a text box (txtContactHist) on the userform.
Using the countIf function I'm trapping zero matches.
The worksheet containing the contacts has the following data columns:
Col A - Customer ID
Col B - Detail of contact
Col C- Date of Contact
Columns B and C contain the data I want to capture
Column A (the customer ID) is the column that is searched for a match ( via the userform text box (txtLic.Value))
To work out the total number of contacts, I use this bit of ...
I have 2 workbookts: 1) Product; and 2) Buyers.
1) In the Products worksheet, every column has data about the books' description like:
A | B | C | D | E
Serial | Desc. | Author | Pubd. | year
Column A contains serial no. of the books, ordered from 1,2,3,4,5,6,7 to .....so on...
Columns B,C,D and E, have other details of the books.
2) In the Buyers worksheet, there are two columns;
A | B
Serial | Buyer Info
These are the details people who bought the particular book order by the serial no. of the book purchased.
Required
I need to make a new column in the first worksheet i.e. Product, say F, which I want to read all the buyers' info of that purchase the book with that serial no. and concatenate them together in the same cell separated by a comma.
what i am trying to do is use concatenate in a vlookup to search for a resource number and date, then return another column in the array.
the formula looks like:
=VLOOKUP(CONCATENATE(D7,$H$6),Roster_Allocation,7,FALSE)
but only results in NA.
if i search for the resource number only, i get the correct result.
also, the res# and date are concatenated in the table array. could this be related to the way excel is storing the dates (40241?) even though both concatenated fields look the same?
i have also tried adding a new coumn which has the res# and dates concatenated as the lookup value but still all NA.
I want to compare two columns. I would like to see if the contents of column B appear anywhere in column A, for any amount of rows, and if it does, to place the match in col C.
So in the example below, red in B1 would be checked in A1 through A1000 or however long A is. When any row has red, place that match in that row for C.
This would be repeated until all rows in B are complete.
Example
Col A: red
Col B: red
Col C: red
Col A: red
Col B: blue
Col C: not found
I'm trying to copy a worksheet and place it at the end. I will rename it afterwards. I'm using the following code but it doesn't work.
Private Sub Copy_sheet()
Dim wbk As Workbook
Dim wsh As Worksheet
Set wbk = ActiveWorkbook
wbk.Worksheets("Template").copy After:=wbk.Worksheets(wbk.Worksheets.Count)
Set wsh = wbk.Worksheets(wbk.Worksheets.Count)
wsh.Name = "New Sheet"
End Sub
Not using VBA is there a way to take the worksheet name and place it in a cell?
I am trying to create a basic order history in excel from orders generated on our limited ERP system. I have exported the data required and imported to excel, but due to the export limitations, I can only export certain fields at a time.
This means that I have to create three sheets for all the data I require.
The first sheet has the bulk of the data and is now nicely collumned. However, I cannot get both the customer code and full customer name on the same sheet due to the aforementioned limitations.
Sheet 1 has customer code and other data, sheet 2 has customer code plus full customer name.
I have created a collumn for the customer name on sheet 1 (A3) next to the customer code (A2)
Basically, what I am trying to get it to do is as follows:
In "A3" I need to create a formula that states that if A2 contains a certain customer code, then look in sheet 2, match the code and place the full customer name for that code. Bearing in mind that we have over 395 customer codes in use, so asking for a specific code to lookup would be as time consuming as entering the codes manually.
i create new sheet on button click event using this code
Worksheets.Add before:=Sheets(Sheets.Count)
ActiveSheet.Range("A1").Value = TextBox1.Value
but now i want that when this new sheet will open there will be some labels, textboxes and buttons n etc will be appear on that sheet so how can i display these elements on new sheet dynamically ??
now I thought about recording the macro, but it's an offset and if you click the chart, then it'll mvoe somewhere else even though the charts are always placed in the middle when generated. So I think I need to place it in a specific range. I want to use XY coordinates or range labels or something to that effect but would like something to this affect:
With Charts.Add
.ChartWizard source:=Worksheets("sheet1").Range("a1:a20"), _
gallery:=xlLine, title:="February Data"
End With
I got that in VBA help, but I'm not using Chart wizard, my code just creates the chart from the data on its own with specific chart type. So anyway, how would I make it put each of my five charts for the 25 people in the same place for every person. Performance Errors etc etc etc all will go in the right place everytime, regardless if you start with the page scrolled. I should do range right, but how do I change this macro to do it without chart wizard...just a chart that's already created and named. QUESTION: How do I place charts that are created from a data source onto a specific area of one worksheet, and those charts are always in the same place regardless of the person'a name.
I have two workbooks, one a database that accumulates data over time, and the other a monthly report that needs to be populated based on information from the database. When moving data between the two, the following works fine (snipped a lot for ease of reading):
Workbooks.Open (database) ', Password:= /
Workbooks(reportbook). Sheets("Sheet1"). Range("A1").Value = month(Cells(282, 5))
As you can see, it simply puts the month from a cell in the database into the report workbook. However, if I change Range("A1") to Cells(1,1) I get a runtime error:
Workbooks.Open (database) ', Password:= /
Workbooks(reportbook).Sheets("Sheet1").Cells(1, 1).Value = month(Cells(282, 5))
I can generally work around this issue by changing the active workbook, but doing so is a big pain. Is there any way to use the second bit of code without popping an error message?
I need to copy/cut and paste an Html table from a website into and Excel sheet without the data being transposed. See link for the table I need to get into Excel:
http://www.voedingswaardetabel.nl/voedingswaarde/?vw=vv
Then cut and paste it to Excel, you see the data is now vertical. I need the table horizontal as it appears on the website.
What is the keyboard shortcut to return to your previous location in the worksheet?
View 2 Replies View RelatedI have a work book with prob close to a thousand individual work sheets in it. I have a goup of cells that are the same in ever work sheet that I would like to pull and place into a master work sheet. I dont want to copy and paste each one, I'd be a hundred before I finished and I dont have that kind of time. Im thinking a macro might be what I need but Im not very good with useing them let alone making one to fit me needs.
View 2 Replies View RelatedI am after a macro to do the following, my visual basic skills are very limited (non existant):- Look at the date in cell A1 on Sheet 'Live Report' and err 'remember it' Copy a range of cells from A3 to A10 on 'Live Report' Go to sheet 'Monthly Summary' and find the date that had been remembered previously (this date will be in column A on 'Monthly Summary' which will probably be a mixture of values and formulas). After the date has been found paste special and transpose the 'values only' copied range from 'Live Report' (A3 to A10) in column B on 'Monthly Summary' next to the date that has been found in Column A.
View 2 Replies View RelatedI want to "calculate" a worksheet name by concatenating 2 cells. Then based on this concatenated word perform a " lookup". Basically I have a form for people to fill in and want to extract their answers automatically.
View 4 Replies View RelatedI have a column that has dates in it in this format:
Friday 07/03/2014
Friday 07/03/2014
Friday 07/03/2014
Friday 07/03/2014
Saturday 08/03/14
Saturday 08/03/14
It lives on Google Docs as well, there are multiple people putting dates in and sometimes people will use just CAPS for the day name.
What I would like to achieve is that if someone puts another Friday 07/03/2014 under the Saturdays in the list, I want it to automatically sort itself under the Friday 07/03/2014 ones.
Whole row would need to move those spots up, not just the one cell.
Column B values are the average of the most recent five values in column A.
The formula in Cell C1 is “=B7” so that the value in C1 is 1.17. My question is: When I place a value in cell A8, which will result in the average of the five numbers in column A4-A8 being placed in cell B8 how do I make the value in C1 change to be the value which is in cell B8 rather than the value which is in cell B7?
Note: I just want to be able to pick up the most recent average value – the
method whereby it is done is of no consequence. However I cannot think
of an alternative. Example:.....
The first five columns of my table contain row titles. I'd like to keep these columns stationary while I scroll horizontally through the remainder of the columns.
As it is now, only the row number remains stationary, but I really don't need the numbers, just the row titles.
Is there any way to do this? And would this also be possible with column headings (so I can scroll down without them moving up)?
What I would like to do is if a string e.g. "*12/18/09*" is found in column A, then place "1" in column B. There are many rows that I would like to search. Either a formula or VBA would be fine.
View 6 Replies View RelatedHow do you make a column automatically place items in alphabetical order?
Also, how do you make a column automatically put dates in order?
I have been trying to see if I can peform the following, I want to be able to have Day 1 (Col B) by using the Start and Finisg Columns Place a 1 in column H for every hour between the Start and Finish times.
This needs to be done for each of the Day in column B, ie 7 Days.
Coulmn's FGH is repeated for the 7 days....
I am trying to place the contents of an array into a column on the sheet. I can enter the array into 1 cell (which strings the values), but I can't seem to make range(1)=array(1).
View 9 Replies View RelatedI have a workbook with 4 worksheet that store different type of data. It also has a userform that load at start of the application which is to search the data in the workbook. The userform has a combobox where the names of the sheets are stored. when the user selects say Sheet2 in the combobox, it enables the relevant textboxes on the userform and activates the worksheet at the change event. The userform has a search button that searches all the worksheets based on the text entered in a textbox.
The problem: how to search based on 1 textbox. What I want is: say for e.g the end-user selects sheet2 from the combobox, this intern enables 4 textboxes (Name, DOB, Nationality, ID #) on the userform. The end-user should have the liberty to enter data in 1 and/or any of the textboxes. The search should be performed, that if data is only in 1 of any of textboxes then give all rows that fit that criteria and display in a temp worksheet. if say the name and dob is filled by the user than what matches both should be displayed in a temp worksheet. if say dob, name and ID# given so the search button should narrow down to fit all 3 criteria and then display result in temp worksheet. As if mentioned data can be entered in either just 1 or any or all textboxes.
E.g. the worksheet is (Columns are Name, Nationality, DOB, ID#)
row 1 = name: Steven Martin, DOB: 27-may-1993, Nationality: Trinidad & Tobago, ID #: 1234567
row 2 = name: Gary Richards, DOB: 2-FEB-1993, Nationality: British, ID #: 456789
row 3 = name: David Cohen, DOB: 27-May 1993, Nationality: American, ID #: 98765
row 4 = name: Roberto McDonalds, DOB 21-Jul-1962, Nationality: British, ID # 654321
row 5= name: Gary Richards, DOB: 01-Dec-1978, Nationality: Australian, ID # 1234567
Now if the user enters only name as "Gary Richards" and search then row 2 and 5 should be displayed in a temp worksheet. if user enters name Roberto McDonald and ID# 1234567 then it should not display anything. if user enters DOB 27-may-1993 and nationality British and ID # 1234567 then as well shouldn't display anything and should a msgbox "no data found".
I've been tackling this data capture/paste issue for a week or so. I found the string below which does provide a good foundation for my challenge. But, my basic level of understanding macros limits my modifications to meet my needs.
[URL] ......
I have 20 worksheets in my master file corresponding to Excel files individual associates will update weekly. After the associates have updated their individual files for the week, I want to capture the data entered and paste values into a master file containing a worksheet for each associate (sharing the same name as the individual associate file). All of these files are housed on team SharePoint sites.
I need a macro to perform several steps after clicking a "Run Update" macro button in the master file:
Open individual associate fileIn master file, search for each Initiative listed in column B (starting cell B3) in the individual associate file (in column B starting at cell B11)If Initiative is found in individual associate file, copy adjacent data in columns D:J for the respective rowIn master file, paste values to the corresponding Initiative row for the corresponding week's worth of dataIf Initiative is not found in the individual associate file, move to the next Initiative listed in the master fileRepeat these steps for each individual associate file
Linking would be the easiest way to accomplish this if I wanted to have a multitude of weekly individual files for the associates. However, I'd rather each associate have one file for them to update (basically overwriting their previous week's entries).
I need to ensure the paste values corresponds to the appropriate day of the week. In simpler terms, if the date in the individual associate file in cell D9 reads Oct 1, 2012, the data captured from that row needs to be pasted to the corresponding row/column in the master file that reads the same date.
I have a long column of info as below. I want to extract (the name changes)- "SEAPORT TWN" etc and also "01.30" and place into separate columns say G and H at the next available cell.
**
SWITCHLIST FOR TRAIN---Grain Spec -East
DEPARTURE TIME from WESTTOWN is 01:00
TOWN STOP---SEAPRT TWN Arriving at 01:30
PICKUPS
Terminal Shipping Burl North 460020 GRAINCAR empty Elevator Co. Ltd.
Terminal Shipping Sante Fe 100396 GRAINCAR empty Elevator Co. Ltd.
Train should leave this town with 2 car(s)
TOWN STOP---PRARIE TWN Arriving at 02:00
SETOUTS
Elevator Co. Ltd. Burl North 460020 GRAINCAR Grn w/Wht Lttrs -4 Bay
Elevator Co. Ltd. Sante Fe 100396 GRAINCAR Maroon w/Wht Ltr -3 Bay
1/05/2007 8:57:52 a.m. ...............................
My issue is that I have 1200+ addresses to make more readable - the first column is the street name, the second is the post code(s) relative to the street - what Ideally I'd like to achieve is one row per street, the street name followed by the post codes... i.e. take this...
A33 Relief RoadRG2 0RR
Abbey SquareRG1 3AG
Abbey SquareRG1 3BE
Abbey SquareRG1 3BQ
Abbey SquareRG1 3FB
Abbey StreetRG1 3AN
Abbey StreetRG1 3BA
Abbey StreetRG1 3BD
Abbots WalkRG1 3HW
Aberford CloseRG30 2NX
Admirals CourtRG1 6SP
Admirals CourtRG1 6SR
Admirals CourtRG1 6SS
Admirals CourtRG1 6SW
Ainsdale CrescentRG30 3NG
Alan PlaceRG30 3BW
Albany RoadRG30 2UL
to this...
A33 Relief RoadRG2 0RR
Abbey SquareRG1 3AG, RG1 3BE, RG1 3BQRG1 3FB
Abbey StreetRG1 3AN, RG1 3BA, RG1 3BD
Abbots WalkRG1 3HW
Aberford CloseRG30 2NX
Admirals CourtRG1 6SP, RG1 6SR, RG1 6SS, RG1 6SW
Ainsdale CrescentRG30 3NG
Alan PlaceRG30 3BW
Albany RoadRG30 2UL