I was wondering if there was an array formula or some VBA code which can transpose/repeat values based on a count. Below is an example of what I mean:
Name Count Output Tod 4 Tod Bill 3 Tod Sue 5 Tod
Tod
Bill
Bill
Bill
Sue
Sue
Sue
Sue
Sue
1. The idea is that the macro will start on sheet1, look at column "Jon", copy the values down to the last active cell as well as the dates and paste special values and transpose them onto the "Jon" tab starting in B8 and B9. Each day all of the dates and values on the "Jon" tab should be overwritten with the data on sheets1 and 2, instead of it being cumulative.
2. The macro will then go to sheet2, look at column "Jon", copy the values to the last active as well as the dates and paste special values and transpose them onto the "Jon" tab starting in B18 and B19. Each day all of the dates and values on the "Jon" tab should be overwritten with the data on sheets1 and 2, instead of it being cumulative.
3. The macro would then save the "Jon" tab as a separate pdf in a specified location.
4. The process would then repeat for "Mike" and "Paul". Each day the number of columns can fluctuate, so it may be 3 one day (Jon, Mike, Paul) and 8 the next. The number of rows also may vary from day to day, and column to column.
I have attached a workbook that contains the data for the scenario above as well as the output (Jon, Mike, Paul tabs). Ideally there will not be new tabs that remain for Jon, Mike, Paul, just a default tab (e.g. "output" tab) that would receive the transposed data from the columns for Jon, Mike, Paul. I put all 3 in the workbook so you could see how each of them would output.
I'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.
My current formula in Summary tab D4:D19 is
{=SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$H$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))
+
SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$I$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))}
This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).
The results in the pink highlighted cells (Summary column D) should be:
Names starting with A - 3
All others - 2
I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.
I have a column that repeats it self and I want to perform a count on Column B, based on the values in Column A.
Example attached : Book2.xlsx‎
I am looking for a formula to repeat a given set of values, a certain number of times ...
See attached for visual explanation ...
I am using Excel 2007, and I'm stuck with this problem.
View 4 Replies View RelatedI have over 300 lines of data that I need to transpose from columns to rows and I am not sure the best way to do it.
I can do it manually 125 times using paste special and transpose and get the results I need but I am thinking there must be a better way.
Example spread sheet inclosed. Column A is what I have - Column C-F is what I need.
I have spreadsheet which consists of two columns of data.
Column A contains a gerneric heading/group and Column B details some results for each heading/group; these results cover between 16 to 40 rows.
Basically, I need to transpose each heading/group's rows of results data into the same row as the heading/group row; then delete the rows where the results data was copied from; then I want to go to the next heading/group in Column A and repeat the process - transposing the results into a row.
I have written a simple macro which seems to work intermittingly - one which doesn't repeat or loop though. Sometimes I get the Runtime Error 1004 and sometimes the macro overwrites the transposed row if the results are listed over 16 rows.
My draft macro code is as follows:
Sub Transpose()
'
' Transpose Macro
' Macro recorded 27/11/2008 by Hunter
'
' Keyboard Shortcut: Ctrl+p............
I have data in rows like this
A B C
1 x y z
2 x y z
3 x y z
4 x y z
5 x y z
6 x y z
7 x y z
8 x y z
9 x y z
10 x y z
11 x y z
12 x y z
What I am looking for is that I want to repeat the rows after every third row in another column. For eg
A B C D E F G H I
1 x y z x y z x y z
2 x y z x y z x y z
3 x y z x y z x y z
I've got two columns, one with policy numbers, the other with error codes.
A policy could be in the list more than once, so I'm counting the number of unique policy numbers with the function
=SUM(IF(FREQUENCY(A10:F10000,A10:A10000)>0,1))
I want to count the number of errors that are NOT code 00 or code 21. Because the error codes are saved as text, I'm counting the number of errors with
=SUMPRODUCT((B10:B10000"21")*(B10:B10000"00")*(B10:B10000""))
The problem is that since a policy number could be listed twice, when I count the errors, I might be counting the same policy as an error twice. This means that I could feasibly end up with more errors than I have unique policies, which doesn't work.
What I'd like to do is only count the number of errors that have unique policy numbers. That is, I only want to count an error the first time it is in the list, based off it's policy number. Is that possible?
Here's my sample data: [URL]
I have a list of 130 names in column A. I have a number value between 0-10 in column B (next to the name). I need to insert the number of rows defined by the value in column B, below each row that I already have (if the value is 0, then the row needs to be deleted). The inserted rows have to be filled with the name value from the row above.
For example - before macro:
Joe Bloggs 2
Adam Wilson 10
Peter Andrews 0
Claire Burrows 6
After macro:
Joe Bloggs
Joe Bloggs
Adam Wilson
Adam Wilson
Adam Wilson
Adam Wilson....................................
I know how to use data validation to prevent repeat values entered into a column on one worksheet using data validation. I don't understand how to do this across 12 worksheets (months of the year) in a file.
To be more clear: When I enter an ID in a column cell, I want that to be compared to all the other ID's in all the past worksheets and rejected if it has already been entered
All are column D The values entered are identification numbers and have both letters and numbers.
I am in desperate need of a function that will count a column of data where there are blanks and values based on a certain date that will also capture any data that is added after refreshing the table from Access. I have tried several functions but this is what I have: =(ROWS('TouchBack Detail'!$Q:$Q)*COLUMNS('TouchBack Detail'!$Q:$Q))+(COUNTIFS('TouchBack Detail'!$B:$B,'Nov TouchBack Summary'!B$1)). The result should be 3 but it’s including all other cells in the column that are not and should not be included in the refreshed table’s data (Table_TouchBack.accdb). I have attached the spreadsheet for review. The function is in cell B27 highlighted in yellow.
View 3 Replies View RelatedI can export inventory data from my Point of Sale system that looks like this:
I need to manipulate it to look like this:
To do so I think I need a macro(?) to:
Copy and insert as many rows as the quantity in Column C. In case of >1 the row is deleted.
This would allow me to print labels for every item in my inventory.
I am able to quite easily count the number of specific values in a cell after the table has been filtered. However, the problem I have run into is that some times the data needs to be placed into the spreadsheet twice (or to be more specific the same subject is associated with several unique data points).
What I need: some way to count the instance of some give value in column D only once based on the presence of a duplicate (unique) identifier in column C. However, when I filter the entire database, it must count *only* the filtered cells and not the hidden cells as well.
Picture:
Column C Column D
111111 M
111111 M
111111 M
[Code]....
Currently calculates: M=9, F=2
Right now it incorrectly states there are 9 "M" from column D when it really should be 5 since 3 are duplicate values. My main difficulty is making sure this continues to work after I filter the entire sheet (say column ZZ) and have a bunch of hidden cells.
Equation currently using to count only filtered values (in this case "males" and "females"):
="M = " & SUMPRODUCT(SUBTOTAL(3,OFFSET(D3:D13,ROW(D3:D13)-MIN(ROW(D3:D13)),,1))*(D3:D13="M"))
i want to transpose the values in a column to a row and when i update the values in the column it should be transposed to the next blank row..
View 10 Replies View RelatedI need a way to find a value in a column which has one or more corresponding values in an adjacent column. Then take all of the corresponding values found and count all occurrences of the found values in another column. But I only want to count the entries if an adjacent column is not blank.
Not the easiest thing to describe. Starting to wonder if I need to think in reverse. I hope the attached example makes more sense.
Book1.xlsx
I have a sheet with 3,000 rows and only two columns. Column "A" consists of 20 to 30 different names, column "B" consists of 50 to 60 different products. I need to be able to evalute the value in column A and copy, paste special transpose all values in Column B that have the same value in column A. As an example if cells A1 through A5 is "Arizona" and cell B1 is Broccoli, B2 is Cauliflower, B3 is Apples, B4 is Oranges, and B5 is Bananas, I want to copy B1 through B5 and paste special transpose to cell C1.
This then would need to loop all the way to the bottom of the data in Column A looking for a change in value. The attached file called Sample Data has two tabs. The one titled "report" shows the raw data, the one titled "Final" shows how I would like the results to appear (column L)
I have a list in Excel, and it has the company in one column, and it's information in the next x rows until there is a blank row (4-7 rows). I get that you can copy the rows under the Company, transpose next to the company, and then delete the contents of the cells that you just took the data from, but I have 6200 rows do to this to, for 500+ companies. I tried recording a macro for two entries, and this is what I have (see below), but how do I modify this so that I don't need to type in every single range, and it will do it to the whole column? The data is in column A, I am posting in column B.
Basics for Macro Requirement:
1. Find the blank cell in row A
2. Skip the next cell/row (this is the company)
3. Select all the cells beneath the company cell, until it reaches the blank cell underneath
4. Copy, transpose these cells next to the Company cell (transpose in column B)
5. Delete the contents in row A that were just transposed
6. Find the next cell with data (company)
7. Repeat Steps 2-6
My recorded Macro:
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+q
'
Range("A3:A8").Select
[code]....
I have a spreadsheet with the following columns
No Of Transactions
Total Revenue
LID
OURSUB
SubCount
Name
Phone
Phone2
Email
Phone3
Phone4
Phone5
Phone6
Phone7
Broker
Bcode
There are 28,000 records in the spreadsheet.
The column LID contains the ID of the client. Each client can have transactions in multiple subs ("oursub" column). The spreadsheet is sorted by the LID column.
What I need is for the "oursubs" values to be transposed column headings for each unique LID. E.g. if "LID" 12345 has 50 records with 25 different "oursubs". I want one row with LID "12345" and 25 columns with the names of the 25 "oursubs".
I am attaching two sample spreadsheets. The spreadsheet "LID_Source.xls" contains sample source data and the spreadsheet "LID_result" shows the result that I want.
I am trying to execute a script I copied from this site to transpose a column of values into unique rows. There was a very similar thread to my question, but the code does not work for my situation as I am a newbie to VBA. The referenced thread was Need to transpose multiple comma separated values
I would like to comma delimit column "D" into unique rows per value while maintaining the relationship with the data in the other columns. Here is the example of my data:
GA-AG-00010-A-2013
Apache Hunting Club
709
36
GA-AG-00020-A-2013
Tiger Branch Hunt Club
1596
71, 72
GA-AG-00030-A-2013
Big "O" Hunting Club
3058
59, 64, 65, 75, 79, 84
I want to make each value in Column D a unique row and still be associated with Column A-C. When I run this code I get a Runtime script error 9.
Sub SplitKeywords()
Dim MyArr, v As Long, i As Long, LR As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
[Code] ......
How to correct this? My actual data spans from column A-Q and can place the "split values" in column Q.
Formula that will repeat a cell number as it drags down and as soon as the number changes. I am using helper column that shows the cell number. I need to drag this down about 1000 rows.
Excel 2010
A
B
C
1
Helper Column
Desired Result
[Code].....
In attached File, there is a table there is date in COLUMN "G" and column "H" contain Name....
I want to count value in column "L" by matching date from column " K " .....BUT THE SAME NAME SHOULD NOT BE REPEAT COUNT... i.e, HITENDRA and RITU name is repeat 2 times in date 1-Jul-2014 .. but it is a similar so it should count as a "1"..... the total count for the day is "6" but avoiding similar value the total count is "4"..... so I nead a "4" figure as a result......
For column "B" count all the 1's if column header is equal to name in column A.
For Column "C" needs to be checked if a value was filled in column "I" if yes then check if in column "L" has a value, count all these values.
I am trying to count the distinct times a value shows up in a column, if another column has a 2 in it.
For example:
Columns
A, B
2,P25
2,P25
3,P5
3,P6
2,P5
The results shoud be: (2) Because I only want to count the P25 one time.
I'm using Excel 2010
I'm working on spreadsheet where I have to match the data on two columns, and then count how many times that same number appears on both columns (see below table) In this below sample, I should get total of 3. (23654 , 12345 and 57963).
A
B
12345
23654
67891
12345
57963
11111
23654
57963
56954
77777
I would need to count unique frequencies in column A, but only for a specified value in column B.
Example:
A|B
1|1
1|1
1|1
2|1
2|1
2|2
2|2
3|2
3|2
I am using Excel 2007 and found this formula that counts all unique frequencies in column A: =SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1))
In my example, result = 3 Now, I would need to add the possibility to count only for B=1; in my example, result should be = 2.
How do I count unique values in Column A only if the values in Column B match?
A B
1 a
2 a
1 a
4 b
5 b
1 b
The formula would return "2" for "a", and "3" for b. Basically, there are names in column B, and I want to know how many unique things are in column A for each person. If there is a better way than a formula (pivot table?) that would be great - like a table that has each of the names (from Column B) and the number of unique items from Column A next to each unique name.
I have two columns. Column A had numbers and column B has names. I need a count from column A for each name in column B.
View 1 Replies View RelatedI think I am working all around this in Excel 2003 and feel like I am in the neighborhood, but can't seem to quite get there. On sheet1 I have 2 columns of 100s of rows. All of the cells in column A will be filled in with a date as time goes by (1-May-09 for example). Several of them may be 1-May-09 as a matter of fact. In column B, some of the cells will have a number in them and some of them will be empty. On sheet2, I want to construct a formula that returns a count of cells in column B that have a number in them based on a specific date in column A. For example:
AB
1-May
1-May777
1-May
1-May
1-May
2-May999
3-May
2-May
1-May
2-May111
1-May
If I could get the formula right, it would return an answer of 1 for 1-May in the example, because only 1 of the 1-May entries in column A has a number in the adjacent cell in column B. A formula for 2-May would return an answer of 2 since only 2 of the 2-May entries in column A have numbers in the adjacent cell in column B, and so on for the month. If I understand things right, there are too many arguments for COUNTIF. It seems that SUMPRODUCT should work, but I can't make it so.