Condense and Compare Values

[wbochar]

I have an Inventory Spreadsheet that I need to compare vesrus an older version, problem is that the newer version has multiple item entries inside it when the older one has them condensed..

Old NewItems Reformed/Condensed
---- ----- ------------------
MX101 1 MX101 2 MX101 5
MX120 12 MX101 3 MX103 14
MX656 6 MX103 12 MX120 14
... MX103 2 ....
MX120 4
MX120 8
MX120 2
MX656 1
....

I need this to work in excel vba, i have the compare function written -- but trying do the condensing of the info is troublesome.

For each item in NewItems range

Compare the items after the currentrow
add them together (if there are any)
write to the reformed sheet with the condensed info

Next

Whats is really busting my brain is the recursive looping that has to happen.. and that I dont know how many entries the same item has -- so I guess I have to search everything except the current and other items I have found.

any thoughts code snipets are more than welcome..

wbochar

 

[TonyJollans]

Hi wbochar,

You don't need to write convoluted code; use the power of Excel.

I'm assuming your NewItems range is two columns (item code and count) and that you have some column headings so that your data starts in Row 2. Let's say it's in columns A and B and extends to row 1000.

1. Find a spare column somewhere, or insert one after column B.
2. In row 2 of that column, enter =IF(A2<>A1,SUMIF(A2:A$1000,A2,B2:B$1000),&quot;&quot
3. Copy this cell down to row 1000 (or whereever the end of your range is)

This will give you the counts you need. If you select the cells with the count totals in and Copy them and then Paste Special > Values to themselves you will have values instead of formulae in the cells.

Now you can use AutoFilter to separate out the rows with non-blank cells in the new column. You can then copy the two columns (item code and total) out to a new sheet or do whatever else you want.

If you particularly want it in VBA you can record yourself doing it.



Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.