So what I really want is a formula to make table 2 from table 1. If there is something in columns Value or Value 2, I want the something(s) and the(ir) corresponding date(s) to be carried over.
You don't need a formula, just use Data Filters. Why not just create a filter for the two value columns. filter both columns by anything non blank That is: select all values then uncheck the blanks filter Now cut n paste what is left as Table 2
made u a macro: sub foo for each cell in Range(Cells(topRow, 1),Cells(bottomRow, 1)) if cell = "" or Cells(cell.row, 2) = "" then Cells(cell.Row, 4) = Cells(cell.Row, 3) end if next cell end sub
in D5, write "=IF(AND(A5="",B5=""),"",C5) In other words, if A5 and B5 are both blank, then blank, otherwise C5. So, if either A5 or B5 is not blank, then C5 will show. I think.
I think you misread the problem, because that macro won't work on so many levels He's creating a new table from an original. And he wants to delete empty rows He just needs to use the Data Filter feature
Fair enough. He said he wanted a formula. Looking at his picture, he obviously doesn't. He needs a filter or a macro to do that.
try this macro Sub foo() RowCount = 2 For Each cell In Range(Cells(topRow, 2), Cells(bottomRow, 2)) If cell.Value <> "" Or Cells(cell.Row, 3) <> "" Then Cells(RowCount, 5).Value = Cells(cell.Row, 1).Value Cells(RowCount, 6).Value = cell.Value Cells(RowCount, 7).Value = Cells(cell.Row, 3).Value RowCount = RowCount + 1 End If Next cell End Sub
Was just fixing your typo. Yeah. That should do it, now that you fixed the typo where rowcount was used on both sides of the assignment
I think what you really want is for us to guess the true nature of this data and the true nature of your... calculation. I vote it is scatological.
Looks like Jontro's chart of which of his two favorite p*rn sites he masturbated to each day, and how many times.
Thanks for the tips. I figured it out, mostly. I just used filters as suggested by heyp, but I was hoping there was an easier way. I was hoping Table 2 could be the final, polished table that would automatically update while Table 1 was my working table I'd have in the background. I didn't want to have to filter out each Value column and copy and paste it to a separate table, then sort the dates from earliest to latest (the dates from the actual data set aren't chronological). Oh well. It was just something I was trying to go above-and-beyond to help clean something up for someone else on my team. I'll tackle it again next week for the next month's report since this month's report is due today. Anyway, thanks all for helping me with my Poop Count Report. (And no, cheke, I didn't get laid off. I was instead given a ****acular job, and over two months I had to run from person to person singing my song of woe that I may be given a job more to my liking. And it happened a few weeks ago, so hooray!)
I think this is still possible formulaically, but it'd probably be way over-built. Your destination table would need if/then logic to decide which dates to populate, and then some vlookups paired with if/then logic to pull the right numbers. It'd have to be a pretty critical table to do all that mess for.
Hey, I just did it with one concatentation formula and a Pivot Table. The Pivot Table will sort the date column, and filter by the Concatenation formula in the original table. I can post screen shots, if you want. It can also grow dynamically as you add to your original Poop Count Table. This might be the easiest way.
sorry, double post. Got to go run an errand, but I'll give you the steps of using a pivot table to do this.
