VBA code to list all products from each customer in one note

Sheet attached.

Seems I've seen this problem once before... anyway, try this...

Please Login or Register  to view this content.

dangelor : Thanks for response. The code works, however, I don't want to delete any rows from data. If there is multiple matches then result should list all results one below the other and copy it. Basically, I need to upload the copied values in SAP against one particular Customer name(I can do the SAP part, I just need the looping in VBA). the copied results should be one below other, something like for "Claire Gute" it should show

FUR-BO-10001798
FUR-CH-10000454

See if this is how you wanted.

Please Login or Register  to view this content.

Jindon, Thank you so much. This is exactly what I needed. Just for my learning purpose, do you mind commenting on steps you followed. Thanks a ton in advance.

Please Login or Register  to view this content.

HTH

Thanks again.

Hey everyone, I need to modify this program to hold 3 dimensions in array. For example, for each customer name, it should give corresponding data for category and Product ID.

As earlier, If there is multiple row for one customer then It should display customer name and Category(if there are 2 category, it should split to next line) only once but matching prodcut ID should be listed in one line. for exampleCapture.PNG

Change to

Please Login or Register  to view this content.

Jindon, this code works for three columns but it is not diving the category into 2 parts for customer who has more than one category. For example "Tracy Blumstein" has Furniture and office supplies. When I run macro it just shows furniture in category column.

Originally Posted by Jimmy1709

It should display customer name and Category(if there are 2 category, it should split to next line) only once but matching prodcut ID should be listed in one line. for exampleAttachment 627896

I just changed the code according to your picture.

Please Login or Register  to view this content.

Jindon, sorry to bother you so much. But, when there are multiple criteria, sheet is keeping all of them but in the same line. However, I need them in different line as shown in picture. Furniture and office supplies should be on 2 lines not in a single line. I owe you for this one bro.

Do you mean?

Please Login or Register  to view this content.

Yes, this is exactly what I wanted. Thank you so much.

Jindon, sorry but need one final help. Can you please comment on the code where it adds new sheet ? and also, help me code to delete the newly created sheet. If I want to newly created sheet to upload in SAP and then delete after upload in SAP complete. Whats the change I need to do in code ?

Please Login or Register  to view this content.

Thanks a lot Jindon.

Jindon, I need one more small adjustment to the code. I tried doing it myself since last 2 days but no luck. Instead of creating new sheet every time, I need the array values to be pasted on Pre formatted sheet with headings( Sheet name be "Upload_data"). In this case, pre formatted headings would Cutomer ID, Category and Product ID. The values from array(skipping array headings) should be copied from 2nd row onwards, when we run the Macro. Can this be done ?

Change to

Please Login or Register  to view this content.

Hey Jindon, This is copying to a existing sheet but the values of product Id are messed up. It is getting random results. They are not sorted as per condition.

They are not sorted as per condition.

What is this?

what condition???

Attachment 628541Attachment 628540

For example, customer Tracy Blumstein, the products and category is not matching with whats in Raw data. See the attached photos. One for actual data(pivot table) and other one VBA output

in the image where heading -customer name is misspelled is VBA output.

Jindon, I fixed that issue. Its working now. But new problem I have is its deleting the content from upload_data sheet before running. I dont want this to clear content, however, if I run the macro twice, it should keep the first run results and add any news rows I have added to raw sheet. The code that worked for proper sorting is below

Please Login or Register  to view this content.

Proper change should be

Please Login or Register  to view this content.

Jindon, this code works. But, when I run macro twice, instead of adding just newly added rows it adds all rows again and duplicating the values.

I don't understand what you want.

Upload a workbook with before/after.

No one can understand what you are trying to do unless a sample workbook.

How are you adding a new sheet, or how do you want the code to determine which sheet to be processed?

Still not clear.

1) Do you want to process data from the scratch every time you add the new data?
or
2) Newly added data should be treated as a different set of data?

If case 2), you need to isolate the new data from the old data.

Newly added data should be treated as new data(but this should all happen in same sheet - as i will be using data from only this sheet to upload to SAP). I just edited my previous response also. The purpose is all about avoiding duplicate entries in SAP.

Then how you adding the new data?
Is it possible to add a blank row(s) when you add new set of data?

e.g.

Old data
(blank row(s))
New data

Just to make it simple to understand, may be when macro runs it should add comment "Yes" in column D of all data rows in "upload_data" worksheet.

When uploading to SAP, I can use the column "D" value as variable for loop. If there is already "Yes" I will skip it. If there is no "yes" then i will script SAP to upload only that data to SAP.

If blank row(s) create lot of complexity in code then we can skip that for now. just consider data as continuous. If not considering blank row(s) in main data would be helpful to have robust Macro.

What you think is completely wrong, it makes easy to modify the code.

Oh OK, I am very new to programming and learning everyday from experts like you and YouTube videos. but, adding a comment "yes" in column D would be necessary. It makes looping SAP script easier. Can that be done ? along with considering the blank rows.

Col.C Yes, no blank row.

Please Login or Register  to view this content.

Jindon, no worries about blank row. but, this code doesnt work even for outputting the results as our earlier code. It skips many data rows. Also, there is no "yes" in column D

Just noticed, its adding "yes" in data input tab. I need that in results sheet "upload_data"

run the code once, it changes col.d to YES
copy data in sheet2 and add it to the bottom, and run again.

Originally Posted by Jimmy1709

Just noticed, its adding "yes" in data input tab. I need that in results sheet "upload_data"

What for?

How do you find old data and new data in Sheet1???

You need to upload a workbook before after for both data sheet and the result, otherwise just wasting time.

Originally Posted by jindon

What for?

How do you find old data and new data in Sheet1???

I need not differentiate old or new data in raw data. I need that to differentiate in output sheet. Hence the requirement of "yes" in column D of output sheet. sorry for making all this confusion.

Originally Posted by jindon

You need to upload a workbook before after for both data sheet and the result, otherwise just wasting time.

Upload just a small amount of data sets, no need more than 10 rows, clearly showing before/after.

Originally Posted by jindon

Upload just a small amount of data sets, no need more than 10 rows, clearly showing before/after.

Here is the attached sheet.

Then what should happen after you add new data in sheet1?

Originally Posted by jindon

Then what should happen after you add new data in sheet1?

That should just copy below this data, I don’t want to delete any data. I wanted to create loop to make it easier but that’s causing lot of confusions. I will be able to get that done myself while uploading to SAP.

I'm confusing more.

Are you adding New data below the Old data in Sheet1 at all?

Originally Posted by jindon

I'm confusing more.

Are you adding New data below the Old data in Sheet1 at all?

Answer is yes, but I think if you give me solution to this now. I can figure that out myself. For now, if macro does Befpre and after as attached sheet that’s sufficient.

Add one line in bold.

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Originally Posted by jindon

Add one line in bold.

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Wow, was that such simple 🙈 I took a hell amount of your time for it. I really appreciate your patience and kindness Jindon. Thanks again for everything. Just to solve your curiosity, I just sent you pm explaining my overall problem which I wanted to attain solution for, I guess I am half way through it: I can close the loop 😊