Excel 2010 Help

[BaconFatty]

Hello all,

so Im putting together a spread sheet and cant figure out how to do something.

What I have is this

What I need is this

Anyone know how to combined rows like this?

Thanks for any help

 

[Janus67]

Could use an if statement.

I'm not exactly sure what you are trying to do.

In the example the date is assigned to 1/2 in pic one then 1/3 in pic 2.

 

[BaconFatty]

I'm trying to combined like rows based on the first row.

In picture 1 you see I have Column "Name" with values 1,2,3. In the first group of data I have Info A and Info B. Then I have another entry for both 1 and 2 with a date. I'm trying to combined it so I don't have duplicates of the name column and all available information is on that row, like picture 2. Where I have everything showing on the same row for that value.

Does that make more sense?

I could always do this by hand, but I have a spread sheet with 2000+ entries and that would take a while.

 

[Janus67]

So I take it the second picture is a typo with the date going to person 3 instead of person 2?

Just making sure I'm not missing something.

I think you could probably do an if-statement and have it check the a# cell?

 

[BaconFatty]

Woops, yes that is a typo, I just fixed it.

I'm not sure how to write that if statement. Would it be something along the lines of(not sure what the code would be).

IF(J5:J6=J2:J4)then copy cell "M" to cell "M" of the correct line

 

[idkfa]

Mmh it would help (me at least) if I knew how the content is filled to the sheet, explaining why there are these gaps.
My first guess though would be using vlookup. I got it working so far, only I am facing is: The result for Info C delivers me 0 because when I ask for the value of Column M for the value 1 in Column J it just searches the first row, finds 1 and delivers 0 because the Column M is empty there. I just don't know yet how to keep it looking forward if it finds no value there.
Only Explanation for this would be performing a if question. Something like if value=0 repeat same search, but this time the search matrix starts in the next row. You would have to repeat that until you get to your very last row. If you have 500 rows this would be a hell of an expression. Again, I have no idea how to shorten that.

However I hope this gave you or some of the others an idea on how to possible do that.

 

[psionic98]

sounds more like a vlookup, which can be a total pain in the rear at times, but well worth learning..

 

[BaconFatty]

Mmh it would help (me at least) if I knew how the content is filled to the sheet, explaining why there are these gaps

The reason for the gaps is we had a spreadsheet sent to a customer with items and prices. Then they returned a sheet with different prices. Normally I would just sort it then copy and paste when I got it matched up. How ever in this case the customers spread sheet has items that aren't on our spread sheet, and our list has items that his doesn't have. So I need to combined the 2 so that I don't have multiples of the same item...


I'll do some googling on vlookup and let yall know if that works

 

[Robert17]

Do both sheets, yours and the customer's, have a common denominator column, i.e., one column where you have something like a part number, that you share? That would be a great start to using vlookup. Otherwise you will need to use if/then algorithms.

 

[idkfa]

Sounds a bit similar to a problem I faced at work, here is how I approached it (since it was a one time thing I did it a way you need some work yourself).
You have your list in the sheet, copy the customers list right next to it.
Then you use =COUNTIF

=COUNTIF($A$3:$A$9;C3)
Looks up if the Value from C3 is somewhere in A3:A9, if yes it posts 1 if not 0

Then rightclick a cell containing 0 and filter for it

Then you have on your customers list only the items, that only appear the customers side. Mark the Items name and price, cut it out and paste it below your original table. Now you extended your original list with the new items. Items that you have, but not the customer are covered since they are in your list already.
Now you want to eliminate doubled items.

H3 contains just =D3 so you copy your costumers prices.
For G3 the code is

=VLOOKUP(C3;$A$3:$D$9;2)

It gives you the corresponding price of your doubled items.
Last but not least you look for identical items and prices

=IF(G3=H3;1;0)

Now you can delete all items from the costumer list that posted a 1 or NV.
Now you just have a list of all double items, with different prices. I don't what you do with them, but as of now, your left table is filled with all items you have in your inventory plus the new ones the customer gave you.
Important: Never delete any rows or cells from "Your List", just add the new items below.
That way, after you are done, you can clear the costumers list again and wait for the next synchronisation and your left table is up to date. You can store it somewhere else (I did it in another sheet) or work from there.

Please let me know if this helped you somehow. I know that VLookups can be a huge PITA especially when you try to go for such algorithms.