Add (every 3rd Or 4th Depending On Content) Rows Into Columns
May 21, 2012
I have a set of data all in clumn A - I want to sort into into rows - "Name", "Manage" or "Send Message", "Job title/Company" and "Country", but some are missing "Country" (therefore, some entries have 3 rows and other have 4.
The differentiator is that every name is repeated within the cell, whereas no other row is repeated. Any macro to get all these into 4 columns?
Example:
Steve Wonder Steve Wonder
Manage General Manager in ZXY games United States Jake Gave Jake Gave
Send message VP Business Development in XYH Games
Russia Carter Jonas Carter Jonas Send message in 148G
Clement Galiay Clement Galiay
View 9 Replies
ADVERTISEMENT
Jun 24, 2014
I have a worksheet which contains 2 columns which is needed to work my problem.
Unique Work ID and Description
The unique work ID are the same for each description, but there are up to 5 different description associated with each unique work id.
I'm looking for an automated process but where to start to convert the 5 rows in the unique row and 5 column for the descriptions
View 8 Replies
View Related
Jul 18, 2013
Im replicating rows which have multiple items in Column1. Im aware that the ID Column has duplicates..my source data is like that for now so I wont complicate it just yet.
Once ive got this working I can proceed to the rest of the tasks
View 9 Replies
View Related
Aug 16, 2012
I wish to sort all of my rows depending on the value in only one of the columns. I do not know how to set this up, my data starts in row 7 and is in columns B:F, needs to be sorted by descending in column B.
View 1 Replies
View Related
Jun 5, 2014
I have a workbook, with a worksheet "Summary". In this worksheet "Summary" I would like to delete all columns that have less than 100 rows.
Please see the attached file : Summary.xlsx‎
View 3 Replies
View Related
Jul 6, 2014
I have lot of data in Excel 2010 which I wish to bring in Columns using a Macro depending on the input value which the macro should prompt me. For E.g.:
A1B1C1D1E1F1G1H1I1J1
12345678910
12345678910
12345678910
12345678910
12345678910
If I select data from A1 and J1 (in practical it will be more Columns) the Macro should prompt me how many Columns would be the output on Master Sheet. If the input is 2 then it should create an output Sheet "Master" and should show the following result
A1B1
12
12
12
12
12
34
34
34
34
34
56
56
56
56
56
.. ..
.. ..
It after selection I run the macro and input 3 then the output should go in three columns (A1,B1,C1) one below other. If 4 is Input then 4 Columns (A1,B1,C1,D1) will come below each other so on and so forth.
View 6 Replies
View Related
Jun 24, 2009
I have some workbooks that have some serials in A row and i need to seperate them depending on how many digits they have,
For example some have 13 digits,and some 8.I just want to seperate them into different rows.
Here is a sample
seperate sample.xls
View 5 Replies
View Related
Jul 14, 2009
on sheet1 cell a1 is there a way to make sheet1,s name appear as the contents of cell a1
View 9 Replies
View Related
Jun 26, 2008
I have a list of sales enquiries 1600 rows long. Each has a brand recorded in cell J (from j4 above is headers)
I have 4 brands
A, B, C & D
I would like to copy the data from the master sheet into 4 tabs lables A, B, C & D depending on the brand recorded in J
View 9 Replies
View Related
May 1, 2008
I have a template I have been asked to amend and I know I need to use Conditional Formatting I just can't crack the formula I need to use. Cell A2 will have either "CD" or "CW" or "IN" input into it
When user inputs either "CD" or "CW" into cell A2, I want cell G2 to have "Margin Movement" automatically displayed. When user inputs "IN" into cell A2, I want cell F2 to have "MV" automatically displayed, and I want cell G2 to be coloured in bright red fill. Then user is to go to cell G2 and input some text as a description, and I want the red fill to disappear once they have input something in there.
I thought I had it licked using IF statements for the "Margin Movement" and the "MV" cells, but I can't get the conditional formatting to work - I assume this is because the cells aren't technically "blank", as they have IF statements in them.
View 3 Replies
View Related
Sep 13, 2005
Is there a way to control the vertical size of a textbox, so that we could type in a List of Instructions to our operator, and the textbox would resize depending on the numbers of instructions in the box. Also the items in the cells beneath the textbox would need to move down, to allow for the resized textbox.
View 3 Replies
View Related
Mar 20, 2014
I have the names of companies in one column, and the amount they owe in cells in the column beside them. I then have a second list of companies that is a subset of the first. Is there a formula that would place the amount they owe in teh corresponding cell adjacent to the compny in the second list? I've attached a sample workbook, Full Company List in column A, amount owing in B, trimmed down list in D and ideally I'd like the corresponding values in E.
View 3 Replies
View Related
Nov 19, 2008
What I need to do is have a cell that will be say yellow until there is information put into this cell. The information could be in the format of text or numbers. The information would not always be the same so it would need to be yellow when there is no information in the cell and another colour or white when there is information in the cell.
View 6 Replies
View Related
Mar 6, 2009
I have a file consisting of two columns, called, "bom ref" and "material"
I need to show the data as indicated in the file, headed required format required.
In effect where I have a 0, that is the material I need to show for every row with a bom ref. of 1,2 or 3, down as far as the next 0 but not including the 0, when I reach the next 0, the material is a new letter, and that letter needs to repeat down as far as the next 0, but not including the 0 and so on.
View 4 Replies
View Related
May 15, 2007
I want to create a macro that make the somme of 2 other columns when the nummer of the first column ist double(more than one ) and put the somme in a new Rows. like this :
A C
23 5
23 12
23 2
45 4
45 1
46 3
in the news the resultat in the same columns it´s like this
A C
23 19
45 5
46 3
I got this code , but I work only for news colums , A and B
Sub test()
Dim a, i As Long, b(), n As Long
a = ActiveSheet. Range("a1"). CurrentRegion.Resize(, 2).Value
Redim b(1 To UBound(a, 1), 1 To 2)
With CreateObject("Scripting.Dictionary")
.CompareMode = vbBinaryCompare
For i = 1 To UBound(a, 1)
If Not .exists(a(i, 1)) Then
n = n + 1: b(n, 1) = a(i, 1)
.Add a(i, 1), n
End If
b(.Item(a(i, 1)), 2) = Val(b(.Item(a(i, 1)), 2)) + Val(a(i, 2))
Next
End With
ActiveSheet.Range("d1").Resize(n, 2).Value = b
End Sub
View 9 Replies
View Related
Dec 11, 2009
Say I have a method that iterates through a bunch of Sheets. I check the name of every sheet, if it starts with "Data", I need to make everything between A4 and AZ500 empty (either by clearing cells or deleting rows doesn't matter how, as long as the result is an empty sheet below A4).
View 2 Replies
View Related
Aug 12, 2014
In A1 I show the following content: 5,12,27,36,124
How can I make this split to show the following layout:
A1: 5,12,27,36,124 B1:5 C1:12 D1:27 E1:36 F1:124
My worksheet has thousands of lines so hoping for a quick and easy formula that I can pull down and copy for the entire sheet?
View 7 Replies
View Related
Sep 9, 2013
I've got the following scenario:A sheet (Sheet1) with some columns that look like this:Billy
Bobby
Sally
Joe
C
D
C
D
[Code] ........
I have a second sheet (Sheet 2) that looks like this:
Totals
Billy
Bobby
Combined
Billy Score
Bobby Score
Billy Cumul Score
Bobby Cumul Score
-blank-
C
C
CC
3
3
3
3
[Code] .......
Columns 4 through 8 are automatically populated via formulas I've created, after Columns 2 and 3 are filled. What I would like is a macro that does the following:
Copy the data from the first column ('Billy') in Sheet 1 and put it into the second column of Sheet 2Copy the data from the second column in Sheet 1 ('Bobby') and put it into the third column of SheetNow my formulas will populate the other columns.Take these "results", all of the info currently in Sheet 2, and then paste (values only) them into a new sheet called "[Sheet1.B1] x [Sheet1.C1]" in this example, it would be "Billy x Bobby" 4. Repeat with every combination of non-duplicated column match-ups (every column gets matched with every column once, and once only):
a. Billy x Bobbyb. Billy x Sallyc. Billy x Joed. Bobby x Sallye. Bobby x Joef. Sally x Joe
So, in the end there would be a workbook with 8 sheets.
View 1 Replies
View Related
Apr 3, 2008
I'm sure this is an easy one, but I have never done this before. I need to know the best way to hide (for example) Columns U:W if cell U3 is blank.
View 9 Replies
View Related
Apr 24, 2013
I am developing a workbook, one part of the book is to track discipline I have a page for this and a userform on the front page to enter data into it. I would like if you will a summary box (list box or whatever is best) displayed on the front page and I would like a line to appear in that box maybe saying john smith requires a warning. I need this to appear if john smith has been entered 3 times into the name column on the discipline sheet however only if the offence is the same 3 times for example 3 lates not 2 lates and 1 non attendance.
After it displays this line in the summary box I would like to be able to double click it where a small userform appears where a user can then select discipline received or something and for this lien to disappear in the summary and all 3 entries in the discipline sheet.
View 9 Replies
View Related
May 15, 2007
I want to create a macro that make the somme of 2 other columns when the nummer of the first column ist double(more than one ) and put the somme in a new Rows. like this :
A B
23 5
23 12
23 2
45 4
45 1
46 3 in the news the resultat in the same columns it´s like this
A B
23 19
45 5
46 3
View 10 Replies
View Related
Oct 18, 2013
I have a ton of worksheets that I maintain for Budget, LE, LE2, LE3. Every time we complete a Budget I need to group the LE Columns, and once we complete an LE, the budget column needs to be grouped and LE column ungrouped. So twice a year I need to make these changes. It is quite cumber some to change this for multiple tabs.
I already have a technical worksheet to dynamically change the date, naming etc. I want to be able to add a cell that will either read 200 (budget), or 300 (LE). Depending on what his cell reads, I want to group and ungroup their respective columns.
View 2 Replies
View Related
Dec 6, 2007
I have attached a spreadsheet.
I have a dump of about 4000 calls (I have minimised the attachment for size sake) that I need to do certain reports on.
The second sheet (dump) has the data in its raw form from our call reporting tool. The 1st sheet (Report) is a broken down version the powers that be wish to see.
My formula needs to meet certain criteria, as you can see I only have 6 columns on the (Report) sheet where as the dump has lots !
The (report) sheet needs to list the Parent Case ID only if the Summary column has BSR at the beginning, if it meets this criteria then it lists the other relevant columns in the (report) sheet.
View 5 Replies
View Related
Jan 30, 2014
I am working on a spreadsheet that will provide count of types of complaints for particular areas over a running time span. I have tried a multitude of formulas but not sure how to write any of them correctly. What I am trying to do is generate a count of area type by whether it is formal or informal. (i.e. I want to know if there are x formal finish issues vs. y informal finish issues and so on.) This information will get charted and be kept "real-time" user input.
Type
Description
Concern
Formal
Informal
[Code] ....
View 6 Replies
View Related
May 16, 2014
I've been desperately trying to figure out a formula that allows me to count the number of rows in a column that have different values, but that only counts those rows in which other conditions have been met. Probably sounds a little confusing, which is why I'll illustrate it with a screenshot:
The file contains a listing of major executives of a variety of different firms in different years from 1992 onwards. In the screenshot for example, you see the executives of AAR Corp from 1992 to 1997. Since my file however consists of 240,000 rows, there's a number of c. 2400 firms with their executives over a time period of 20 years each. Since not necessarily all executives remain with their companies for the rest of their lives, some executives show up for a number of different firms in my sample.
I want to determine at how many firms each executive worked within his current industry up to the respective year, based on the information of the 2400 companies and executive data over 20 years. The current industry is classified by 1 digit SIC codes (column U) and each firm has an individual firm identifier which is the CUSIP in column S.
For example, when applying this to the first row in the screenshot (Ira Eichner), I would need a formula that counts the number of different CUSIPs (thus firms), given that the EXECID (executive ID, identifier for each executive; column I) equals 9248, the 1 digit SIC code (column U) equals 5, and the Year (column J) is below 1992.
View 7 Replies
View Related
Jan 11, 2010
I am trying to hide or unhide columns E:BL depending if the the cell value in row 54 contains a value (Note cells in row 54 contain formulas). I have written the following code but get a runtime error on the else statement.
Sub UpdateCashflow()
Application. ScreenUpdating = False
ActiveSheet. Unprotect Password:="LD"
ActiveSheet.Select
Dim c As Integer
For c = 5 To 64
If Cells(54, c) = "" Then
ActiveSheet.Range(Cells(54, c)).EntireColumn.Hidden = True
Else
ActiveSheet.Range(Cells(54, c)).EntireColumn.Hidden = False
End If
Next c
ActiveSheet.Protect Password:="LD", DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.EnableEvents = True
'
End Sub
View 4 Replies
View Related
Jan 5, 2009
Can a macro be used to:
1. Delete rows that contain certain text in a worksheet ?
2. Highlight a cell a colur based on a response ?
I have attached an example of what i mean .....
In the Audit Protocol worksheet is the main information - which contains questions and answers .... If a yes, n/a or no is selected then a response is automatically generated in the observations column (thanks to SHG for helping me with the formula for this) ..... How do I then get the cell to change to red if a NO response only is selected ?
Then the information from the Audit Protocol worksheet (the observation column and number column) are copied across to the worksheet named Action List (this is done just by the copy function) .... How do I go about deleting rows that contain "no action required" - as these are not needed for the report to be generated ?
View 11 Replies
View Related
Nov 16, 2009
I need a macro to duplicate a box i have in a sheet depending on how many rows the other tab has. In this example file ive included the macro i have now. With this macro i select A1 : R23 and press ctrl shift d, it asks me how many duplicates i want and i enter the number based from how many the test2 tab has. I would like a macro to read how many rows the test2 tab has and then automatically duplicate the box that many times . In other words automate it, instead of me having to enter the number ( this number changes depending on the file i have ).
Ive included a test file with intructions and my current macro, i have 2 tabs which need to be looked at.
View 5 Replies
View Related
Oct 26, 2012
I have a long (190,000) list of customer data, all in Column A (unfortunately with blank rows among it, but working now to fix that).
Down the column, individual customers are bookended by a "adf" and a "/adf". (these have open and close brackets like HTML code, but I cannot reproduce them in this forum).
For each customer, I need to find the rows that begin with (brackets spelled out since I do not know how to show them):
1. [open bracket] vehicle status
2. name part=3D"first"[close bracket]
3. [open bracket]name part=3D"last"[close bracket]
4. [open bracket]email[close bracket]
5.[open bracket]phone time=3D
6.[open bracket] name part=3D
And transpose only those rows it into columns.There is a dynamic number of rows for each customer, so there's no way to simply count and transpose, as the columns would all be mis-entered.Somehow it needs to recognize those 6 row items, and transpose those values only, with the and the only telltale of a start and finish of a specific customer.
EDIT: How about a macro to delete all rows except those that contain those partial values above?
View 3 Replies
View Related
Jan 23, 2013
Here is an example list:
Networks
Hardware
Networks
Software
Networks
Resources
Apps
Hardware
Apps
Software
Apps
Resources
Domain
Hardware
Domain
Software
Domain
Resources
Print
Hardware
Print
Print
All I'm trying to do in the second column is something that can say if there is a change in column A, then restart the order, or not..
View 9 Replies
View Related
