Need a New Formula for Inserting Rows (or something)

[Guest]

Okay, I'm pretty new to Excel, but I know my way around a computer well enough that I should be able to figure this out with some (preferably detailed) instruction.


So at work, we run these error reports. When our numbers don't match up, we put both different report results in two columns, side by side. Then we make a formula =match(a2,b2,0) and try to match up all the information exactly. When it runs into an inconsistency, it's usually because there's a new bit of information. So what the person training me does is insert a row above the problem, deletes the cell in the right column (b), and that space pushes everything else down so it lines up until the next error, leaving the error marked as #N/A, which we sort by later to view our errors.


I hope that makes some sense...


Anyway, so my question is, since that process is time consuming, is there a shortcut? I'm sure there's a better way to do that than the row thing, but I was trying to figure out if there was a way to either insert a row or cell with an IF formula or something...


Yeah, I think I'll leave it to you guys to figure out.


Let me know if you need more data, but hopefully you can work magic with my vague description.


Thanks!!!

 

[NARAYANK991]

Hi ,


The more details you give , the better the problem can be solved. I have a few doubts :


1. Is the error report only two columns ?


2. Do these two columns have numbers or text or both ?


3. What is the end objective of putting the two columns side by side and doing a comparison ?


4. Would you really want a comparison report which lists :


a) Those items in column A which do not exist in column B

b) Those items in column B which do not exist in column A


Narayan

 

[Guest]

Well, I just started working at the place/using excel a few days ago, so I don't have a ton of information, but I'll see if I can explain a bit better.


1. Well, I forgot to mention before, we run a different error report in a different program, and copy/paste the information into excel. When we run our report, we're comparing two different reports to look for inconsistencies. We take the same section from both reports and put them into two columns so we can further compare them, I guess.


2. Both numbers and text in both.


3. We're looking for any mistakes we made throughout the week. For some reason, even the person training me can't really explain the whole process, either, so we're both kind of in the dark. But from what I got out of it was that we're comparing our work throughout the week to some kind of master copy of what everything is supposed to be. We're trying to get the numbers to match at the end. If they don't, we know we did something wrong.


4. Uh, yes, I believe that's what we're looking for.


Basically, it seems like we have a very roundabout way of getting this information and it's very, very time consuming.

 

[NARAYANK991]

Hi ,


There was a similar question posed in this forum , some time back ; can you go through this topic , and see whether a VBA solution is acceptable ?


http://chandoo.org/forums/topic/move-data-after-conditional-formatting-and-identification-of-duplicates#post-61303


I doubt that insertion of rows can be done using formulae , so VBA would be the way to go in this problem.


Narayan

 

[SirJB7]

Hi, Symphony!

Regarding last assertion from NARAYANK991's last comment, I agree with him and I'll add that I'm sure that you can't perform a row insertion process using formulas, since when you insert a row its cells contains blanks and if you have a formula you're not getting a blank.

There are other ways to simulate insertion but working on another area of the workbook or worksheet (DESREF, INDEX, ...) with which you can display blank rows between a group of rows with data, but they're not actually inserting any blank row, just displaying it.

Hope it helps.

Regards!