Appling a range of data to a range of individual cells

[dubliquid]

HI,

I am trying populate a range of products to a range of customers so that the whole range of products can be applied to each customer.

In the files below I have a selection of the customers and products. I want to combine the two so that it looks like tab 7

combined.jpgproducts.jpgcustomers.jpg

Your help would be appreciated.

[:) Sixthsense :)]

Instead of screenshots, please attach a sample workbook with expected output for better understanding

[dubliquid]

Sample workbook..xlsx

Hi Sixthsense,

Please see the workbook attached. I did it manually for this example, but there are over 900 customers.

[vipul.halbe]

This can be done with a macro

[:) Sixthsense :)]

Try this code...

Please Login or Register  to view this content.

[dubliquid]

Thanks, however I'm not that advanced a user to know what to do with this code. Is there a simpler way, or can you explain?

[:) Sixthsense :)]

I have attached your sample file with Macro Code for your reference.

Refer the below link to know how to insert VBA code in Module
http://www.contextures.com/xlvba01.html

[dubliquid]

Thank you, that worked.

My next problem is that I have combined this information with sales history and now there is duplication. I don't seem to be able to delete the duplicated lines that have a zero value. The program seems to randomly select a line and delete it whether there is sales history attached to it or not.

How would I remove duplicated lines and keep the lines with a value in it.

Please see attached.Sample workbook..xlsx

[:) Sixthsense :)]

Try this in Duplicate file.

Select Column-A & Column-B and Data>>Remove Duplicates>>Ok...

[dubliquid]

Tried that before, it removes the duplicates in those two columns, however it does not remove the remaining information in each line along with the duplicates. All that happens is that Column A&B shortens and mis-aligns the data to the right.

[:) Sixthsense :)]

Oopss... Just select the whole range of data and in Remove duplicates Give Tick for Column-A & B alone which will do the task perfectly.

Please check and confirm

[dubliquid]

Ha Ha, yes I've tried that too. If you look at the top of the sample, next to Annual total there is the number "274". That counts all the value in the Annual total column. As soon as you do it as you suggest above, the number drops to "257", meaning that it is deleting rows that have values in them, and I need that data to remain and the duplicates that are empty to be deleted instead.

[:) Sixthsense :)]

Insert a new column in Column-C

In C2 Cell

Please Login or Register  to view this content.

Drag it down...

Copy and paste the column-C formula as values using paste special.

Now Apply filter for column-C and select "Select&Delete" and delete the entire row...

[dubliquid]

I just get the words "ReduiredData" in each column

[:) Sixthsense :)]

Refer the attached file for details

[dubliquid]

Thanks, found the problem. It was in the way I sorted the info. It is tracking now.

Thank you very much for all your help. You're a legend!!!!