Concatenate Cells And Delete Rows With A Twist

Jun 5, 2009

I have a spreadsheet with three columns (check #, invoice #, vendor #). If a check pays multiple invoices, there is a separate row for each instance of invoice #. (ie if a check pays 10 invoices, there are 10 rows all with the same value for check # and vendor #, but different invoice #s). I need to concatenate the multiple rows to just one row per check #, with all the invoice #s in separate columns on the same row and delete the duplicate check # rows.

However, I can only use 10 columns, so if there are more than 8 invoices matching the same check #, the 10th column will need to contain invoices 9-n concatenated and separated by a space. I have over 5000 rows in the spreadsheet.

View 2 Replies


ADVERTISEMENT

Delete Duplicate Rows, With A Twist

Dec 4, 2008

I can delete duplicate rows easily enough, but I can't seem to capture a couple variables where I would not want to delete the selected row. There are no headings, all data starts in Row 1.

I start in Row 1 Columns C and D, compare that to Row 3 Columns C and D (Every other row). If it is a duplicate entry in both columns, delete that row and the row below it. Pick up at next row and continue. See attached Excel file (Excel 2003). The rows with fonts colored RED are the only rows from this data that should get deleted.

View 2 Replies View Related

Concatenate Cells And Delete Duplicates

Apr 1, 2009

I have an excel 2007 spreadsheet that lists
items on multiple rows for the same customer.

Each sheet will list anywhere from 25 to 100 individual customers.

I need to identify the duplicates, concatenate
the "Items" to a single cell on the first row, and
delete the duplicate rows. eg:

Cust #| Item
1 | A
1 | B
1 | C
2 | B
2 | E
3 | A
3 | C
3 | E

The result should look like:

Cust# | New Item
1 | A, B, C
2 | B, E
3 | A, C, E

View 14 Replies View Related

Find If Duplicate Values Exist In A Column, Concatenate Cells And Then Delete

Dec 17, 2009

I want to do, is search column A for claim numbers that match. When I do have a matching claim number, I want to concatenate the original cells ownership field with the said matching cells ownership field (or move into a column in the same row, I can always concatenate later). Once that is complete, I want to delete the row I took the information out of.

I want to join this data in ArcGIS, but as of right now, it's not a 1-to-1 relationship, so only a relate works. That doesn't help me as I want to display claims by ownership, and this can vary per claim. Company A may have 100% on one claim, and then split another claim 50% with Company B.

This causes a double entry on the claim field in this current spreadsheet I have, which requires me to clean it up by making multiple columns of ownership vs. an additional row for shared ownership.

My problem:

Column A Column B
1235555 Company A (50%)
1235555 Company B (50%)
1235556 Company A (100%)
1235557 Company A (33%)
1235557 Company B (33%)
1235557 Company C (33%)

What I would like to see

Column A Column B Column C Column D
1235555 Company A (50%) Company B (50%)
1235556 Company A (100%)
1235557 Company A (33%) Company B (33%) Company C (33%)

View 3 Replies View Related

Count Blank Cells In A Column With A Twist

Jan 24, 2014

I have a spreadsheet that has 36 columns and 6000+ rows. Each column has a mix of blanks and content, which is an X. See example of the pattern below, column A. I want to run the COUNTBLANK function through the entire column A so that it assigns a value in column B, next to the content it relates to. Line B4, B9, and B11 would have results, in the example below. I want to be able to apply a formula the entire worksheet, all 36 content columns, and over 6000 rows, so that I can obtain the results quickly by filling down, across, etc. I suspect a loop might be in order. Currently, I have to run the COUNTBLANK between one X and another, repeating that tediously over and over again.

---A----------------------------B
1 X
2 blank
3 blank

[Code]....

View 10 Replies View Related

Concatenate Cells For Duplicate Rows

Aug 3, 2008

I was trying to create this certain tool using excel which queries data from database. I was successful in obtaining the data I need and paste it to my spreadsheet, however, there are some requirements I cannot create a solution with. Below is a description of my Excel file (which is also attached):

- There are 9 columns on my spreadsheet
- Number of records per data refresh are indefinite, for information in database are constantly being updated
- Several cells on the first column have exactly the same information

The requirements I cannot do are:

- For all cells on first column that have similar values, their corresponding data on column 5 should be concatenated
- Delete rows that have same data for column 1 except the first record found, place concatenated data on column 5 of first record

Example:..................

View 4 Replies View Related

Concatenate Non Blank Cells But Use Concatenate And Substitute Instead Of IF

Aug 11, 2013

Sampling table :

one
two
three
four
one
two
three
one
two
one

Desired results obtained via IF =IF(B2>0,A2&" , ",A2)&IF(C2>0,B2&" , ",B2)&IF(D2>0,C2&" , ",C2)&IF(D2>0,D2,"")

one , two , three , four
one , two , three
one , two
one

Is there any smarter, shorter formula via Concatenate and Substitute or other formulas ?

My closest match, but not good enaugh is =SUBSTITUTE(CONCATENATE(A2&", "&B2&", "&C2&", "&D2), ", , ", " ")
[ returna 2 commad ]
one, two, three, four
one, two, three,
one, two
one ,

View 9 Replies View Related

Delete Rows With Cells That Have Same Value

Jul 18, 2014

I have a sheet with rows that extend to about Column AT. In some of the rows, all the cells are populated with "--". I want to delete these rows. Other rows have "--"s but not in every cell. I tried to remove all the "--"s then running this code:

For t = 2 To Workbooks(WorkbookNm).Sheets("--").UsedRange.Rows.Count j = CStr(t)
If Application.CountA(Workbooks(WorkbookNm).Sheets("--").Range("A" & j).EntireRow) = 0 Then
Rows(t & ":" & Workbooks(WorkbookNm).Sheets("--").UsedRange.Rows.Count).Delete
GoTo Step15__2:
End If

It worked, but I had trouble re-adding the "--"s. So I'm wondering if there is a simple way to modify the above code, so that it looks for and deletes rows with only "--" and possibly nothing in the cells (e.g. Column AU and on)?

View 9 Replies View Related

Delete Rows With Cells < #

Jun 29, 2009

I'm trying to create a macro and having absolutely no luck with it. I want the macro to run through all the rows and delete all rows where every number in that row is less than a specified number (.03 for this particular case).

View 19 Replies View Related

Delete Rows Where 2 Cells Are The Same

Apr 24, 2008

I m trying to develop an IF function that will go through each cell in a column and delete an entire row based on whether the Cell Value is identical to the one above or not. What i have thus far:

Sub RemoveDuplicate()
Dim i As Integer
Sheets("Pre-Allege").Activate
i = 2
Cells(i, FindCol + 2).Select
Do Until IsEmpty(Cells(i, FindCol + 2))
If Cells(i, FindCol + 2) = Cells(i - 1, FindCol + 2) Then Rows("i:i").Delete Shift:=xlUp
Else
i = i + 1
End If
Loop
End Sub

View 4 Replies View Related

Delete Rows With Cells That Contain Some Word?

Mar 4, 2014

How can I delete all rows that have a cell that starts with the letters APP

View 3 Replies View Related

Delete Every Cells That Has Data Except For First Two Rows

Mar 9, 2012

What is the fastest way to delete every cells that has data except for the first two rows?

I currently have this code but I find it a bit slow (and everything is replaced by column A to Z [which meets my need right now]).

Code:
Sub delete()
Application.ScreenUpdating = False
lastrow = Activesheet.Cells(Rows.Count, "A").End(xlUp).Row
Range("a3:z" & lastrow).delete
End Sub

There must be a better way.

View 5 Replies View Related

Delete Rows Where Cells Don't Contain Specific Value

Feb 27, 2013

If I have a column of data called "ColStream" and want to delete all rows that don't contain the string "Production", whats the best way to achieve it?

I have tried a for each loop, with INSTR but as this moves down a cell from the cell whose row has just been deleted, skipping the "NEXT" row in the range, it doesn't give the correct results.

Incidentally, the end game is to copy all the remaining cells to the cell immediately below a cell called "StreamsDatabaseStart" in a worksheet called "ProdStreams" should anyone out there be feeling particularly charitable!

View 4 Replies View Related

Delete Rows Where 2 Cells In Same Row Is Blank

Oct 18, 2006

I want to compare the data in column B and C of Row 2 through X (X being up to 20,000), and if BOTH B and C are blank, delete the entire row.

View 4 Replies View Related

Delete Rows With Blank Cells

Jan 8, 2007

I need a Macro that will delete rows within a specific range that contain blank cells or preferably delete the rows where the first cell in the row contains a blank cell.

View 9 Replies View Related

Delete Rows With Some Blank Cells

Jan 17, 2007

I went to the Macros page and pulled this macro to remove rows if they have blank cells:

Sub DeleteBlankRows2()
'Deletes the entire row within the selection if _
some of the cells WITHIN THE SELECTION contain no data.
On Error Resume Next
Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
On Error Goto 0
End Sub

I ran this and nothing happened. Does anyone have a fix or perhaps a better way to make this happen?

View 2 Replies View Related

Concatenate Duplicates + Delete + More Steps

Oct 15, 2009

I have 2 columns of data with 7,000 rows that I am trying to manipulate in the following way. The first column contains a list of car manufacturers (not really my example, but involves the same concept). The second column contains a list of different models of cars. For instance,

Ford | Taurus
Chevrolet | Avalanche
Saturn | Sky
Ford | Explorer
Honda | Accord
Ford | Taurus

What I want is a list of 2 columns with UNIQUE car manufacturers in first column with a list of UNIQUE car models (comma delimited) in second column. So for this example, the first row could read " Ford | Taurus,Explorer". I don't want Taurus to appear twice. I got a lot of help from JBeaucaire from this post. I just can't add the ability to remove car models from the list also. Thanks in advance for all the help!

View 7 Replies View Related

Delete Rows Of Data If Cells Contain Same Value From Another Sheet

May 30, 2014

I have got a set of data in a workbook with Sheet 1 range A1 to K373 and a range of data in Sheet 2 ranging from A1 to A30. What I will like to do is to have a VBA code to loop through all the cells in Sheet 1 column C and check which are the cells which value matches those in Sheet 2. Those that matches in Sheet 1 will have the entire row deleted. I have come out with the following codes but it does not work.

[Code] ..........

View 3 Replies View Related

Delete Unwanted Rows Based On Value In Cells?

Sep 28, 2011

I am aware this is probably very simple, but I haven't been able to find a usable macro yet. I would like the data in the workbook that is not "Tuesday" (column L) and is not "3" or "4" (column I) to be deleted. All rows that have "Tuesday" and either "3" or "4" should not be deleted. Each row that is not deleted must have Tuesday in column L. All other cells are blank intentionally. The workbook will remain the same titled WKBK1 (no need to put the output data in a separate workbook nor separate spreadsheet).

Do you know what the macro would be so I don't have to copy and paste a formula over a large number of cells?

WKBK1
A B C D E F G H I J K L 2
3

[Code]....

View 8 Replies View Related

Simple Delete Rows Based On Cells Value

Apr 3, 2013

I am simply trying to delete all rows that have a value of less than 16 in the cell value's first 2 characters (column G).

Start with:
18N
18K
16K
16K
16K
14L
14L
13L
12L

End with:
18N
18K
16K
16K
16K

The code I am using:

Sub DeleteUnneededRows()
Dim c
For Each c In Worksheets("Sheet1").Range("G1:G500").Cells
If Left(c.Value, 2) < "16" Then
c.EntireRow.Delete
End If
Next
End Sub

but it is not working.

View 7 Replies View Related

Macro To Delete Rows In Which All Cells Contain Same Or Single Value?

Sep 3, 2013

I need a macro to delete each row in which all cells contain the same value, always starting in cell G2 and going out an indefinite number of cell's. So, for one run, the range could be G2:BU2 and another it could be G2:PW2. Also, the number of row's is indefinite.

And an added complexity: I need it to ignore the cell in this search process if the cell contains "NC" - so if all the data in the row is the same except for a few that say NC - then it gets deleted. If the any of the cells in the row have other values, then that row does not get deleted - even if it contains "NC".

View 2 Replies View Related

Macro To Delete Rows Where There Are Blank Cells

Sep 25, 2013

I would like code that will delete all rows where there are blank cells in Col D from row2 onwards.

View 2 Replies View Related

Duplicate Cells Delete BOTH Rows In Excel

Nov 12, 2013

I have a report with about 7000 rows in it. I need a macro that will find all rows where column A and column B are the same as another rows column A and column B and delete both rows.

View 4 Replies View Related

Keep Only Doubles Of Cells In A Column, Delete All Other Rows

Aug 21, 2009

I need a macro that keeps any row that has a cell value that shows up twice and only twice in column A. All other rows would be deleted (except the header row, row 1). For example, If it's found only once, a third time or more than three times, delete those entire rows.

This is different than "keeping only duplicates" which would be 2 OR MORE.

A couple of things,

1. The workbook might not be sorted by column A, so a cell named "ABC0011" could show up in row 3, then again in row 8, in this case, keep both.

View 9 Replies View Related

Delete Empty Columns And Rows Up To Used Cells

May 13, 2006

I am looking for something that will first delete all empty columns up until it runs into data, then delete all rows that are blank in Column A until it runs into data.
In other words, I recieve different spreadsheets that start their data in different locations and I would like to have them always start in cell A1.

View 4 Replies View Related

Delete Rows And Columns Entirely From Worksheet So It Shows No Cells

May 9, 2014

Please see this attached image: HowTo Do This.PNG

I have this worksheet that I did not create and notice it has entire rows and columns completely gone / deleted. For example, the only columns it has ranges from A to AI and then no more columns exist in the worksheet. For the rows, it has rows 1 through 77 but then it shows a gray background.

How did they do this? What is this called? How can I do this to Worksheets?

View 4 Replies View Related

Macro To Delete Rows That Have Cells That Don't Contain Values From A List

Mar 7, 2014

Each day I am going to have a list of about 300 different ID numbers, which i have already got a macro for creating, that outputs them into a single column on a sheet.

The next challenge Ill face now, is that I'll have a list of maybe 500 ID numbers in another spreadsheet.... I need a way to basically tell excel to keep rows that contain the numbers in 1 column on the list of 500, that correspond with those on the list of 300.

Numbers that are not found on the list of 300 ID numbers, must be deleted, along with the entire row.

I essentially need a macro that runs something like this that i found online, but instead of it just looking for the word "apple" as rows to delete, i would need it to check to see if the number is one of the 300 on my list.... and delete the row if it is not on the list

PHP Code: 

Sub Delete_Rows()Dim rng As Range, cell As Range, del As RangeSet rng = Intersect(Range("A1:C20"), 
ActiveSheet.UsedRange)For Each cell In rngIf (cell.Value) = "Apple" _ThenIf del Is Nothing ThenSet del = cell
Else: Set del = Union(del, cell)End IfEnd IfNext cellOn Error Resume Nextdel.EntireRow.DeleteEnd Sub 

View 3 Replies View Related

Macro To Delete Entire Rows If Certain Cells Are Empty

Mar 5, 2009

I'd like help in creating a macro that deletes an entire row that has emtpy cells in col B, C & D in the same row.

So for example if I have empty cells in b3,c3 & d3 I'd like the row deleted.

I've used the code below for just column B but I need to include column C & D as well. I tried putting Columns("B:D") but it deletes everything.

View 14 Replies View Related

Delete Rows With Blank Cells In A Specific Range

Feb 23, 2010

I need a VBA to delete rows with blank cells within columns F - AZ
Columns A - E contain headers but also need to be deleted if cells in columns F -AZ are blank.

View 9 Replies View Related

Delete Rows With Blank Cells But Housing Formulas

Sep 1, 2009

I have several lists that I need to be able to print out periodically.

The main worksheet that underpins each list is over 900 lines long and numerous calcs and formulae populate columns to provide the data for my 'print lists'.

I need my 'print lists' on seperate worksheets and I have linked the data from my main worksheet results, using IF statements to stop unwanted results being displayed by making the rows appear empty (i.e. to display "").

Due to the design of my main Worksheet it is inevitable that I have many rows in each 'print list' that are not required for that particular 'print list'.

I want to remove the 'empty' rows from the print sheets at the time of printing. I cannot use the GoTo > Special > Blanks > Delete Rows because the rows are not recognised as being 'blank'.

I have attached 2 screenshots of my 'print lists'.

P.S. I am not assuming anything but I have searched on OZGRID and it appears to me that I might need to use Excel/VBA?

View 9 Replies View Related







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