Reducing Multiple Rows - Duplicates In Column A

Nov 13, 2006

I can't figure out a solution.

4 columns, thousands of rows.

Column A has alphanumeric data that can appear, once, twice, 3 times or 4, but never more than 4.

I want to see this spreadsheet so that each piece of data in column A is presented only once.

The information in the other 3 columns isn't important for what I want to do, so I need to reduce 20000 + rows, which includes many duplicates in Column A to about 5-60000, but need help figuring the correct formula/macro ....

View 9 Replies


ADVERTISEMENT

Identify Duplicates Across Multiple Colums And Rows

Dec 22, 2008

I have a spreadsheet with 20+ columns. Each column contains a list of invoice numbers, eg, 1234. The lists are all of different lengths (see below). I want to find a way to find/highlight/identify those invoice numbers that appear more than once in the entire sheet (eg if invoice 1234 is listed in column B, and also in column H, and/or if invoice 5678 is listed in column A, and also in column J, and also perhaps in column D, etc.)

I've searched this forum but haven't found anything about searching for multiple duplicates in multiple columns. Ideally the matches would be highlighted in different colours, (eg all cells that contain 1234 would be red, those that contain 5678 would be blue, etc., for ease of identification) but I'm not sure if that's possible. I've attached an example that I've shaded myself to show what I mean. I've tried the MODE function but that only tells me which invoice number occurs the most.

View 5 Replies View Related

Hide Duplicates In A Column But Showing All Rows Data

Dec 17, 2012

I have to show a report with 3 columns= Physician names, Section and number of procedures. The problem is that physician name repeat several time according the section they order and I need to show in the first column each Physician name just one, see attached picture.

I did try to use conditional formatting and advance data formatting but not working properly for me. I was able to use =A2=A1 conditional formatting and hide duplicates physician names but shows only the last row of the duplicate names no the first one.

View 4 Replies View Related

Merge Duplicate Cells Or Keep Duplicates In One Column If One Of Rows Has Data

Apr 7, 2014

I have a huge document that looks like this

Column A______Column B_____Column C
100/12__________B___________$
100/12______________________@
100/12______________________€
250/13______________________€
250/13______________________$

I want to keep in ColumnA all three rows of 100/12, because it has a value in Column B in one cell-which is the criteria, and remove the 250/13 because it has no value in cell B.

I was assuming that merging duplicates in column A, and than remove empty from ColumnB.

View 2 Replies View Related

Conditionally Format Rows Based Duplicates Only Found In First Column?

Dec 6, 2013

I'm trying to conditionally format rows of data based on duplicates in the first column, then filter the results. I have a table of data with mutiple variables assigned to different "headings" that looks a little something like this:

A
B
C
D

[Code]....

[selects the table, then GoTo Special selects the blanks, fills in the blanks with the cell above, then Copy and Pastes As Values the entire table again to fill everything in]

I’m stuck on how to input a formula into the conditional formatting window that will:
Format the text to white in columns A, B, and E based on there being a duplicate above that row in Column A only. i.e. conditionally format the values with a * below:

A
B
C
D
E

[Code]....

If I use the conditional formula I found: =A1=A2, then cell E3 gets made white text when I don’t want it to, hence the “referencing column A” part of the question (Column A is always a unique ID number whereas Column E can have a duplicate in the row above). [Edit: Why can't I type Enter or put a line break here... I'll try re-edit at home...] When I go to filter on Column C for YYY again, the conditional formatting needs to realise to un-white the text, which is my next headache because it means conditional formatting that acts relative to hidden rows as a result of a filter... Oh and did I mention the client wants this done in 2003? This is an afterthought though – as I can force them to use 2010 if need be

View 6 Replies View Related

Retrieving Variable Rows From Another Worksheet: Reducing "Calculation Time"

Aug 10, 2009

I'm using this formula to pick all rows from a huge range of cells "DATA" in another sheet, by matching the first column value (SAPDATA is the range that contains all the first column cells of DATA).

{=IF(ROW($B49)-ROW($B$48)>COUNTIF(SAPDATA,$D$4),"",INDEX(DATA,SMALL(IF(SAPDATA=$D$4,ROW(SAPDATA)),ROW($B49)-ROW($B$48)),2))}

All possible first column values have been stored as a dropdown in cell D4, and whenever I change the choice in the dropdown cell, the retrieval of those hundreds of rows takes ages in excel.

View 9 Replies View Related

Inserting Multiple Cells And Shifting Down Column Multiple Rows In IF Statement

Sep 7, 2012

how to shift data in a column down multiple rows while in an IF statement. I am assuming you cannot just simply repeat the insert cell formula.

Here is my code - it's ugly but it was working when I just needed it to move down one cell:

lastrow = Range("A2").End(xlDown).Row
For i = 2 To lastrow
Range("B" & i).Select
If Range("B" & i).Value = Range("B" & i).Offset(-1, 1).Value Then

[Code].....

View 1 Replies View Related

Delete Rows Where Multiple Column Meets Multiple Criteria

Sep 29, 2011

Need to create a macro?

Delete rows where multiple column meets multiple criteria.

detail:
delete rows where
Column H is less than 10000
AND
Column C is empty(blank)

Those 2 criteria have to occur at the same time..

View 1 Replies View Related

VBA Excel To Transpose Multiple Rows And Multiple Column?

Jul 3, 2013

I was planning to transpose this sample data into the output designated below.

Original sample: (There is an empty row after every 4 entries)

Name: xxx
Email: xxx
Phone Number: xxx
Address: xxx

[Code].....

View 9 Replies View Related

Find Duplicates In 1 Col Then Copy Entire Rows Of These Duplicate Rows?

Feb 2, 2014

The below code compares the Data in a Field that must be set and collect the duplicate Values in a second Worksheet.

The thing I want it to copy the rows, when a duplicate is found in Col A. editing the code below:

Original Sheet:
"A" "B" "C" "D"
Teil1A11000
Teil1B21001

[Code]....

View 7 Replies View Related

Reducing List To One Of Each Type?

Jan 17, 2014

Supposing I have a list where each data set appears multiple times. How do I by formula make a list of the unique values?

I've uploaded to show what I mean.

View 11 Replies View Related

Reducing File Size

Sep 19, 2012

I have a spreadsheet which is currently about 10 MB, I would like to reduce this as much as possible.

I am using lots of Vlookups but on each I am only referencing the data I need, there are also quite a few macros.

I paste value all the formula I do not need.

View 5 Replies View Related

Reducing File Size

Jul 3, 2006

I don;t know if there is a way around this but we have a Excel workbook that holds around 14 worksheets taht is getting too large. It currently is around 2.5mb and we constantly use it as a template which is completed and emailed. Sometimes we have to email 4 of these all together and so teh email is too large, even zipping them has next to no effect on the size. The sheet that seems to causing the problem is one that has around 500 lines of formula across 20 columns. Most of these fields have conditional formatting and its this taht is boosting the size.

View 6 Replies View Related

Transposing Column Into Multiple Rows

Feb 16, 2010

I've got an issue where I'm trying to transpose data from one column into several rows. I've been looking for a macro to help me out but can't seem to find a way to do this. Does anyone have any idea how I can do this?

Ideally, the macro would be written so it would find the data in the column and move it to a new sheet and then it would repeat the process throughout the document. The macro would know that the data is grouped b/c of the blanks found between the data set. So as the macro is running, once it hits a blank, it would then copy and transpose the data and continue. Does this make sense?

I've posted a sample of the info I'm working with.

View 12 Replies View Related

VBA Getting Data (5 Column To Multiple Rows)

Feb 22, 2014

figure it out thus problem with macro,

before/sample data :
name
address
gender
activity
code

[Code]...

after macro :

george
g
t
jas

[Code]...

it's possible with macro??

View 3 Replies View Related

Single Column -> Multiple Rows

Mar 5, 2014

I have a single column of about 1500 points, and I need them to be separated into many rows of 20 numbers.

I have multiple issues:

1) The middle number of each row (10th and 11th) need to be repeated. So a transpose formula can not work all the way through the 20 numbers. Unless I transpose the first ten, then tranpose the second ten. Is there an easier way around this ?

2) The bigger issue is, I would like to write a formula that can manipulate the date to enter in the correct cell.

To illustrate I will use a set of ten numbers:

1
2
3
4
5
6
7
8
9
10
11
12

INTO
1
2
3
4

5
6
7
8

9
10
11
12

View 9 Replies View Related

Reducing A Date To A Single Number

Mar 30, 2007

I would like to take a date and reduce it to single number. Eg.

Reduce the day to a number. Reduce the month to a number. Reduce the year to a number.

Add the 3 together. This gives you a number.

Reduce this number to a number.

e.g. today's number date 30 / 03 / 2007
Day is 30
Month is 3
Year = 9

Sum of these is 15 which reduces to 6.

3+0+0+3+2+0+0+7=15
1+5=6

Basically 9 is the highest number. The number 13 would be 1+3=4 and so on. The idea is to return a single number between 1-9.

View 14 Replies View Related

Reducing Width Of Command Button

Jan 8, 2012

i want o make some command buttons on a sheet with the width 50 and when you move the mouse over to any one the width of that one should increase to 300. when you move he mouse over to the other one the size of the previous one should return to 50 and the new one (now under the mouse) should be 300.

i have written the code for it how ever it tends to increase the width twice i.e. it will increase the size of the command button, then reduce it to 50 and increase it again. this action makes it look like it flickering

Code below

Code:

Option Explicit
Dim sShapes As Shape
Dim ActShapes As Shape
Dim wsStart As Worksheet, WsNew As Worksheet
Dim wd
Dim mybutton
Dim ShapeChk As Boolean

[Code]....

View 2 Replies View Related

Reducing Resolution Of Worksheet Data

Jan 16, 2008

I have a piece data acquisition hardware which stores data every 5 minutes into an excel sheet, I need to reduce the resolution of the data to every 30 minutes, currently I am doing this by deleting five rows leaving one and repeating the process, there is however 9285 rows
I am assuming there is a macro which can do this far quicker than me.

View 9 Replies View Related

Reducing Number Of If Statements In Code

Sep 3, 2006

i have a simple if statment

If cboname.Value = Sheets("Data Sheet"). Range("E2") Then
txtpay.Value = Sheets("Data Sheet").Range("F2")
End If

this need to be done for a large list of names and input into another sheet. what i wanted to know was if there is a quicker way of doing this code without repeating the code loads of times

View 9 Replies View Related

Transposing Multiple Rows To One Single Column

Apr 12, 2005

I frequently have to work with tables set up like this:

A1 A2 A3
B1 B2 B3
C1 C2 C3

All I would like to do is to transpose them into one single column:

A1
A2
A3

B1
B2
B3

C1
C2
C3

View 5 Replies View Related

Transposing Multiple Rows And Columns To One Column

Jul 27, 2013

I have a spreadsheet that calculates data in multiple columns (A:AN) and each column has 27 rows of data called "Sheet 1". As it stands, the spreadsheet goes out very far. On "Sheet 2" I would like for all of the data in "Sheet 1", columns A:AN to be "transposed" to "Sheet 2" in column A only. So, column A on "Sheet 2" would have the data from "Sheet 1" A1:A27, B1:B27, C1:C27, so on and so forth. I would like for the formatting to maintain the same order as well.

An example would be:

Sheet 1
Column A B
Row
1 Green Red
2 Blue Yellow
3 Black Cyan
4 Pink Magenta

[code]....

View 3 Replies View Related

Merge Column Data Over Multiple Rows

Dec 6, 2008

I know how to merge data in a CSV (see attachment) from 2 columns into a single cell using =A1&" | "&B2 so that A1 (Safety Products) plus B1 (Fire Protection) becomes Safety Products | Fire Protection in cell C1, but how can I do this across multiple rows so that each pair of names is combined in each row? The job I'm working with is a product CSV file that has 6370 lines so I don't want to do them one line at a time!

View 2 Replies View Related

Transposing Multiple Rows To Single Column

Feb 16, 2012

I want to transpose multiple rows to single column and I'm dealing with huge data.

For example, I am working with data like this

A1 A2 A3
B1 B2 B3
C1 C2 C3

and I have to transpose to

A1
A2
A3
B1
B2
B3
C1
C2
C3

I'm using following vb code:

Sub ToOneColumn()
Dim i As Long, k As Long, j As Integer
Application.ScreenUpdating = False
Columns(1).Insert

[Code] ........

The script terminate without completing the job and shows error as "runtime error '1004': application -defined or object defined error".

View 8 Replies View Related

Converting Data In One Column To Multiple Rows?

Mar 5, 2012

I have a spreadsheet in one column contains multiple sets of data separated by commas see example 1.

I need convert the data to rows, each set into a new row see example 2.

Example 1: PS-8800-C,PS-8800-D,PS-8800-076,PS-8800-077,PS-8800-077A,PS-8800-078,2"-SC-4057-A-40H-GT

Example 2:
PS-8800-C (Row 1)
PS-8800-D (Row 2)
PS-8800-076 (Row 3)

View 4 Replies View Related

Data From Multiple Rows Into Single Column

Oct 19, 2007

I am trying to take data from multiple rows and columns (matrix) and reformat it all to fit in a single column.

It would be great if I could select the cells I want to reformat into the single column as the number of rows and columns containing the original data is not always the same (ie 20x20, 21x35, 56x200) etc...

View 9 Replies View Related

Calculating Reducing Daily Pro Rata Amounts

Dec 5, 2011

Here's my problem.

Charter default premium is a calculated as a rate on outstanding charter hire.

Therefore if charter period is 1 year and daily hire is 20,000, outstanding charter hire (policy limit) at inception would be 20,000 x 365 = $7,300,000

However, by day 365 outstanding charter hire and prevailing policy limit would only be 20,000 x 1 day = $20,000

The premium is calculated on a daily pro rata basis on the reducing outstanding amounts.

I've calculated the premium on the attached spreadsheet assuming hire is monthly not daily, but it would be too laborious to try and calculate that for 12 months or more on a daily basis. What I'm hoping is that there's a formula, covering the range of data, presumably using the calculation on the first day and the last day, but which cuts out all the intermediate steps.

View 3 Replies View Related

Combobox And Radio Button Reducing In Size

Sep 17, 2012

I have an issue with a combo box and radio button.

Anytime I click on either of them, they reduce in size.

It's as if it is reducing by the same ratio every time until you can't click on it anymore.

View 3 Replies View Related

Reducing Decimal Points Without Rounding Up The Numbers

Jul 25, 2006

I have columns of geological data in number form which may have about 4 or 5 decimal points. I want to reduce them to 2 decimal points without rounding the numbers up. Is there a simple way to do this?

View 4 Replies View Related

Get All Unique Combinations From 6 Columns (multiple Rows) Into 7th Column

Jul 2, 2014

I have a table with 6 columns (A to F) and multiple rows each, with cells containing words. Taking the words in any one cell from each of the columns in order from A to F will form a complete sentence each time. I need a solution to display all unique possible combinations in column G.

The number of rows is different for each column. A successful result in column G has to include cells from all columns (A to F).

I searched this forum and found a few analogous questions/solutions, but nothing close enough for me to apply to my case. I tried using a concatenation formula, but I have to manually edit the formula in each cell to get all unique combinations (and that would mean thousands of times). If I just drag the formula down it will increment all cell rows instead of one cell's row at a time.

Here's an example : all possible unique combinations.jpg

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved