In the attached WB I managed to write some code (behind Sheet1) to change the Source table, itself, into the requested layout. My question is - could this be done by ONLY Worksheets functions - preferable without any helper column - if possible. note that all 4 columns data should be transported to the target table. The upper left cell of the target/new layout table can be put in cell G1 or in cell A15.
View 4 RepliesI'm attempting to list my data... I have a dynamic table that has the following columns:
number of countries
country 1
country 2
country 3
1
Egypt
3
USA
Egypt
Scotland
2
Scotland
USA
As you may have guessed, I want trends on these countries. That would be easier to do if I had a single "Country" columns but I have to work that way.
I would have wanted to work with a pivottable (because they're so "user-friendly" (not always though^^)) but I didn't find a way to do it.
The reason why I want to work with a pivottable is to be able to link my countries results to the rest of the table.. If that's not possible, I'd still want to be able to reference them and say "USA, egypt and scotland pop up 2 times"
I found the following formula (in E2):
Code:
=INDEX(Table1[country 1]:Table1[country 3];MOD(ROWS(E$2:E2)-1;ROWS(Table1[country 1]:Table1[country 3]))+1;INT((ROWS(E$2:E2)-1)/ROWS(Table1[country 1]:Table1[country 3]]))+1)
Which works but I have to manually click-drag that cell down to complete my list. I'd rather have it grow automatically if I have to work that way.
How do I rearrange the data columns in a pivot table? I have a table with year to date months, open PO's, & budget numbers, but I'd like to change the order in which they show up in the pivot table.
View 2 Replies View RelatedI have a problem whereby I have a list of data that has a date, a transaction and a balance. I want to be able to rearrange this data into multiple tables, one for each month.
The pictures below explain the situation better, on the left is the list of data I need to sort, and on the right is how I'd like the data displayed. So for the May columns I would like to display all transactions that happened in May and, depending on whether it is an income or an expense, the amount in the corresponding column.
i.e. so the finish result looks something like this:
I've been given the task of automating a spreadsheet to assess whether work has been done by each employee. Every employee has their own spreadsheet, where column A is a job code, and column H contains either y or n dentoting whether they have completed the task (y) or not (n).
The main spreadsheet is designed to show any outstadning tasks across everybody so it has a list of everybodies names in column A and then any incomplete tasks will be listed from columns b onwards next to the appropriate person. Here is what I have done at the moment.
I working on a macro that re-arrange a table, but can not find the code. In the attached file, my original data are on the Sheet1. I would like a macro that create as many sheet as there are different values in Column D and rename each sheets by that value.
View 3 Replies View RelatedIm developing one excel vba file to an entity .
My main objective is speed and automatization in the competition organization.
Im almost finishing it but now im stuck in this situation:
I have a table with 13 cells
A: athlete name
B: athlete weight
C: random number (raffle)
and then from 1 to 10 is the final Groups.
E.g
Name
Weight
No
1
2
3
4
5
6
7
[code].....
Now i need to create a macro to display the itens in the groups when the weighing is finished...
E.g
Name
Weight
No
1
2
3
4
5
6
7
8
9
[code].....
The conditions are:
The numbers should be displayed in the athletes groups
The difference between all the athletes weights shouldn't be > 3 kilos If theres is more than 5 athletes per group the heaviest should go to next athletes group and pop up one message informing that maximum athletes per group was reached...
I need to add data from b2:b12,d2:d12,f2:12,h2:h12 and to show the totals in L2:L7
View 5 Replies View RelatedI have a question regarding Excel 2003.
I have to complete a task using Excel. Normally I would complete the task using a database, but this one needs to be complete using Excel (for others to use afterwards)
I have a flat semicolon delimited text file which holds x amount of lines OR I can get the data using an ODBC connection (directly from the DB)
Each line starts with a date, a name, a second name, and a price.
See: 1.input.jpg
200080221;AAA123;excel;0.34765
200080221;BBB123;access;0.16278
200080221;CCC123;powerpoint;0.35894
What I would like is the date on the horizontal row and name and second name as headline, and the price as the data.
See: 3.result.jpg
How can I solve this using Excel?
I have a column of dollar amounts (variances) and next to each is the shift they belong to.
I am looking to get a break down of the variances for each shift.
Attached is an example of what it looks like.
I have a column of dollar amounts (variances) and next to each is the shift they belong to.
I am looking to get a break down of the variances for each shift.
Attached is an example of what it looks like.
I have a selection of data that lists sellout information for 45 different Cable networks. What I'm hoping to accomplish is to have (on a separate sheet) a function that will automatically pull out the top 10 networks based highest sellout percentages and list them in order from highest to lowest. I don't even know where to begin or if it is even possible to set up as an automatic function.
View 3 Replies View RelatedAttached is a sample of a excel file I am using for tracking overtime.
On the sheet labeled tracking I want to gather data of the other sheets based on a date range.
All sheets other than the tracking sheet will be labeled in the date format mm-dd-yy.
On the tracking sheet in cells B2 & B3 will be the date range that I want to gather the data from.
There is 3 columns of data that I want to gather, "Balancing" "Approved" "Unknown".
I would like to return just a sum of these from the dated sheets M3:O42 based on the employee name A3:A42
I have a what I believe to be a consistant formula on lines S12-S18 for gathering the sumproducts for dates on column O. Only problem is that the sumproduct formula is not working on line S18 correctly. I keep looking it over and I can't figure out why it is not returning the correct amount for that cell.
Here is the formula I am using:
=SUMPRODUCT((TEXT($O$12:$O$500,"dddd")=R18)*1)
Please see attached line S18 for the error I am getting.
Trying to learn the basics of how to pull data, from the same formatted excel spreadsheets, combined into one consolidated spreadsheet. I created a few examples below of what I am trying to do. Eventually, I want to be gathering data from over 200 spreadsheets at one time. I believe that a MACRO is needed to do such a thing. I am not sure of the complexity of the MACRO, so hopefully I will be able to mimic whatever needs to be done in my actual file. I have almost ZERO experience with MACROS, so if this is way above entry level MACRO.
Below I have 3 spreadsheets, and I want to collect the data from those 3 spreadsheets into the consolidated spreadsheet.
I am starting an excel file that extracts file from another worksheet, then after it is extracted, A2 cell has a value in cell B2, I wanted to copy all the cells in column A&B to column C&D in such a way that when the word "check is encountered in column A, it will arrange itself in the last with the other cells that contain same words before the word "check". I wanted to do this in macro or formula if possible.
View 12 Replies View RelatedI want to delete alternative row in a worksheet or sorted so that I only have the following arrangements..
I have Data in Rows which as followings
Row 1
Row 2
Row 3
Row 4
Row 5
.
.
so on till 1000 data
I don't want Row 1, 3, 5 .... so on
The result should should be arranged as follows:
Row2
Row4
Row6
.
.
.
so on
My data are in cells A2:E7 I have 3 options first one is always in column A and rest 2 options are distributed in 4 columns B-C-D-E
I need to arrange all them to left as shown In G2:I7
ABCDEFGHI1FranJoneMichalPedroJoseLeft1Left2Left321Z71Z737Z17Z14Z17Z1751Z71Z7617Z17Z7Z17Z17Sheet1
I am starting a data sheet that I will be pasting from the internet into my data sheet daily, the problem I have is that the content data is pasting with blank rows between data and also there is one piece of data that is situated on the row below which i need moving to the end of the main data row, the idea is to have single data rows with no blank rows.
I have attached an example sheet showing the layout it repeats with only the amount of data that changes.
I have colored the data as follows:
Grey= Blank Rows
Yellow= Data Move to end of main row
Green= Unwanted Data
I have attached a work book to this to show what i want to do.
On sheet 'lorry 1'.
I have data in columns g to m.
I need that data to transfer to the lorry sheet as per example on sheet 'what it should look like'
It needs to segregate days and leave a line clear in between drops as per example
lorry 1.xlsx
i got data something like this...
m4-1
m4-10
m4-100
m4-2
m4-3
m4-30
i try to arrange it in ascending order...
m4-1
m4-2
m4-3
m4-10
m4-30
m4-100
I have 2 columns of data:
Column A is SKU #
Column B is Bin Capacity
I would like to take each SKU and its corresponding bin cap and arrange them into a 16 column and 9 row table on another sheet.
For example A1, B1, A2, and B2 of the old sheet, should become A1, B1, C1, D1 of the new sheet. Also I need there to be a line gap between each table.
Anyone know how to do this?
This is what I got so far:
I want to know is there any way to arrange bars in the chart with respect to their magnitude (ascending or descending) without making changes in the source data?
View 5 Replies View RelatedI'm trying to write code which allows me to cut and paste arrange of data from one sheet to the next.
the critical field is MRPC column i, I need to select all data that when I apply a auto filter allows me to cut and paste from the line below the header. And repeater using the a value
>=200
Cut all cells, rows paste.
then repeat this
>=155 to 199
>=145
PlantMaterialMaterial DescriptionSLocBatchL/OBMRBulkMRPCBinTypeUnrestr.BUnOpen QtyQual.Insp.BlockedMtyp100029PCV00703AAASADDLE CLAMP COVER1000R1120018.0008.0000.0000.000#N/A#N/A
I want to take a list of phone numbers in a single column(800,000+ rows)and spread the numbers across the page horizontally to fill the page. Each of the new rows will have probably 4-5 phone numbers. below is an illustration:
original:
column A
row1 5551234
row2 5553432
[Code].....
i want to pick data from every 2 columns and arrange it vertically, one under the other ;
sample data:
A 579751 579800 52151 52175 126721 126750
B 546451 546500
C 608971 609000 508081 508110 548941 548970
E 962701 962750 24851 24875
desired outcome:
A 579751 579800
52151 52175
126721 126750
B 546451 546500
C 608971 609000
508081 508110
548941 548970
E 962701 962750
24851 24875
I have a long list of accounts (for the purposes of of bookkeeping - keeping a cash disbursement journal) and I use data validation that uses this list when I enter an account's name in the journal. Because the list is very long, I often times have to scroll up and down the list in the drop down menu that comes up at a cell I need to populate and when looking for the proper name of the account I needed to be there. I am looking to add a capability to my data validation list use when I would be able to enter several symbols, part of the name of the account I am looking for, and the list of accounts displayed for me to choose from would get shortened based on the symbols I enter. the symbols I enter do not necessarily have to be the first symbols of the account name I am looking for. (as sometimes, I do not remember how exactly the account is called - and so I just guess part of the name ... )
View 10 Replies View RelatedI have built a reporting dashboard to track emplyee efficiency .Within this dashboard i have produced a point scoring league table . I am lookinf for a forula that will automatically work out who my top performers are and arrange them in them in the form of 1st , 2nd ,3rd etc . I believe the answer to lie in the form of a pivot table , perhaps with a macro to populate the data , but would be grateful for come direction
View 3 Replies View RelatedI would like to have a macro to automatically generate a statistics table (on the "statistics" tab) with the 5 following fields:
Fragment names / # samples / # of failed samples / % of success / # of variations in the fragment (SNP). At the bottom of this table, I would like to have a cell with the average % of success for all fragments. The data to generate these statistics are on the "gene name" tab (please note that this name will change every time I will work on a new gene). To make things easier, I think the macro should be run from this tab.
1. The Fragment names are displayed in row #5. I use one column per variation per fragment. If one fragment has 3 variations, there will be three columns and I will merge together the fragment name cells. The fact that some cells are merged can be a problem when copy-paste to the stats table (as I would like to get rid of the merging).
2. # of samples corresponds to the number of cells in blue in column A. The number of samples can change from one report to another but is always constant in the same report.
3. # of failed sequences. In the table, I type "Failed Sequence" (if the analysis has failed) and "Missing Sequence" (if the analysis has not been done). When a sample is failed or missing, it is for the who fragment, no matter how many variation there is in the fragment, so I usually merge the cells of all variations for this failed sample.
4. % of success: this is quite easy #sample/#of failed+missing sequence for this fragment
5. # of variation is equal to the number of variations for this fragment (can be 0, 1, 2, etc.). When there is no variation in a fragment, I put '-- in all cells of the corresponding fragment on the "gene name" tab. Fragment 3 on my file is an example of 0 variation.
I was wondering if i could arrange some sets of data (on one sheet) in fixed format and create a new sheet for each set of data using VBA
I have some sets of data in sheet (similar to the sheet "Raw data" of the attached file)
first four rows contain costumer-1 details,
next few rows contain bill details of costumer-1,
again,
next four rows contain costumer-2 details,
next few rows contain bill details of costumer-2,
and
table goes on for around 10-20 costumers
Now, I want to arrange the given set of data of each costumer into a fixed format (as sheet "reminder pad" of the attached file) and create a new sheet for each costumer
Also I want to add reference no to each sheet (with automatic increment for each sheet)