Re: VBA CODE: Delete BOTH duplicate Rows. (Delete duplicate value AND original value)
How can I use this to look at 3 separate columns instead of just the one? I want to delete all rows (including original) when columns A, C, & D are an exact match
Re: VBA CODE: Delete BOTH duplicate Rows. (Delete duplicate value AND original value)
How can I use this to look at 3 separate columns instead of just the one? I want to delete all rows (including original) when columns A, C, & D are an exact match
Re: Multiple IF statements to VBA code
Got that part - thank you! Now how do I get it to copy down for all existing cells?
I want to add 4 IF statements to an existing vlookup in my VBA code, but Im having a hard time figuring out how. I can write formulas, but I am very new to VBA
The formulas are
=IF(B1=B2,"Y","N")
=IF(AND(K2="Y",J1<>J2),"NOT OK","OK")
=IF(AND(K2="START BIN",OR(AND(B2=B3,L3="NOT OK"),(AND(B2=B4,L4="NOT OK")),(AND(B2=B5,L5="NOT OK")),(AND(B2=B6,L6="NOT OK")),(AND(B2=B7,L7="NOT OK")))),"MISMATCH"," ")
=IF(OR(M2="MISMATCH",AND(B2=B1,N1="SELECT BIN")),"SELECT BIN"," ")
Each formula will start on row 2 (same sheet) and I want to populate all the way down the column where data is present (each import has different # of rows)
my vlookup code from one sheet to another below works great, but I cant figure out where to start to add the if statements. Any help would be appreciated.
Sub formulas()
With Sheets("Sheet1").Range("G2", Sheets("Sheet").Cells(Rows.Count, "G").End(xlUp))
.Offset(, 3).Formula = "=VLOOKUP(G" & .Row & ",'sheet2'!$A:$D,3,FALSE)"
.Offset(, 3).Value = .Offset(, 3).Value
End With
End Sub
SOLVED - and it was a simple mistake. I set the macro to open instead of import, so it was creating a new file instead of staying in the one I wanted.
I have a Excel workbook with multiple sheets and a macro that opens and formats at txt file, but it doesn't bring the data back into my spreadsheet - it opens a new text file. I thought I could copy the data and paste it back top my spreadsheet, but I can only get as far as coping. I cant seem to find a way to paste it back. My original file name is MAX.xlsm and the sheet I want populated is Sheet1
Re: meeting 2 sets of criteria to enter value
Thank you!!!!
I need to insert a cost based on 2 sets of criteria. If the disc type is "cd" and the qty is less than 5,000 then $1 - if its over 5,000, the $2. If the disc type is "dvd" and the qty is less than 5,000 then $3 - if its over 5,000, the $4. I cant seem to get everything right and need help!
[TABLE="width: 192"]
[TD="class: xl63, width: 64"]Quantity [/TD]
[TD="class: xl63, width: 64"] Disc Type[/TD]
[TD="class: xl64, width: 64"] $$ [/TD]
[TD="class: xl65, align: right"]1500[/TD]
[TD="class: xl65"] CD[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl65, align: right"]1700[/TD]
[TD="class: xl65"] CD[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl65, align: right"]500[/TD]
[TD="class: xl65"] CD[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl65, align: right"]7400[/TD]
[TD="class: xl65"] dvd[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl65, align: right"]2800[/TD]
[TD="class: xl65"] CD[/TD]
[TD="class: xl66"] [/TD]
[TD="class: xl65, align: right"]3100[/TD]
[TD="class: xl65"] CD[/TD]
[TD="class: xl66"] [/TD]
[/TABLE]
Re: numbers separated by commas that I need to verify exist on another sheet
What if I want to know if all but 1 of these numbers exist? Is it possible to look at it that way?
Re: numbers separated by commas that I need to verify exist on another sheet
Thanks Luke! You are awesome!
I have a large file with a list of numbers separated by comma's on one sheet. I need to verify all numbers within that combination exist on another sheet. Can someone please help me figure this out? I have attached a file that shows what I meanforum.ozgrid.com/index.php?attachment/64829/forum.ozgrid.com/index.php?attachment/64829/.
Re: Find multiple rows that match criteria
That works perfect! Thank you so much.
Re: frequency count
lol ... thanks XOR LX. That is what I needed!
I have a column with multiple numbers separated only by a comma. I want to count the frequency of each number without splitting the numbers apart. Is this possible?forum.ozgrid.com/index.php?attachment/64819/
Re: Find multiple rows that match criteria
forum.ozgrid.com/index.php?attachment/64817/I thought that did what I needed, but there are so many duplicates. I am attaching a bigger sample to show you what I mean. Sheet 2 has the data and sheet 3 the results.
I have a spreadsheet with over 500 Product numbers and I want to find all of the box numbers associated with each Product Number from a separate file. The file I am looking up from is set up as below with the products in the same cell separated by comma.
[TABLE="width: 426"]
Order#
Box #
[/td]Products
[/td]192334
[/td]-10
[/td]4098,4259,4324,4399,4434,4458,4460,4485
[/td]194389
[/td]14
[/td]4098,4259,4328,4434,4454,4485
[/td]195779
[/td]19
[/td]3574,4098,4259,4350,4434,4458,4460,4485
[/td]197348
[/td]24
[/td]3200,3244,4324,4350,4399
[/td]197911
[/td]3
[/td]4098,4259,4357,4398,4434,4485
[/td]194747
[/td]2
[/td]4098,4259,4357,4434,4454,4485
[/td]
[/TABLE]
Product number 4098 should return Box Numbers -10, 14, 19, 3, and 2.
First - do I need to put each product number in its own cell (there can be as many as 30) and second, how can I obtain multiple results? Everything I try will just give me the first instance and that is only when there is just one number in the cell.forum.ozgrid.com/index.php?attachment/64813/
Re: lookup value with multiple listings and return sum of range
Thank you so much!!! Works perfect!
Hopefully i can explain what I need. I have a list of item numbers (A) with amounts of orders (B) and locations (C). How can I look up D1402 to return how many times column B is 1 and column C is between 6-10? For example my first search of D1402 should return 2, D1403 should return 0
[TABLE="width: 271"]
A
D1402
[TD="align: right"]B
1[/TD]
[TD="align: right"]C
7[/TD]
D1402
[/td]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
D1403
[/td]
[TD="align: right"]2[/TD]
[TD="align: right"]7[/TD]
D1403
[/td]
[TD="align: right"]1[/TD]
[TD="align: right"]93[/TD]
D1403
[/td]
[TD="align: right"]1[/TD]
[TD="align: right"]54[/TD]
D1403
[/td]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
D1403
[/td]
[TD="align: right"]1[/TD]
[TD="align: right"]31[/TD]
D1404
[/td]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[/TABLE]