As AI cannot solve this we need humans to solve this. So I have been given stupidly formatted data and I need to convert it into system friendly data so I can automate reading and save huge amount of time and minimize the errors.
So I have data following:
| Blank | Client1 | Client2 | Client3 | Client4 |
|---|---|---|---|---|
| ProductId1 | ProductAmount | ProductAmount | ProductAmount | ProductAmount |
| ProductId2 | ProductAmount | ProductAmount | ProductAmount | ProductAmount |
| ProductId3 | ProductAmount | ProductAmount | ProductAmount | ProductAmount |
| ProductId3 | ProductAmount | ProductAmount | ProductAmount | ProductAmount |
I would like to have the data following format:
| ClientID | ProductId | ProductAmount |
|---|---|---|
| Client1 | ProductId1 | ProductAmount |
| Client1 | ProductId2 | ProductAmount |
| Client1 | ProductId3 | ProductAmount |
| Client1 | ProductId4 | ProductAmount |
| Client2 | ProductId2 | ProductAmount |
| Client2 | ProductId3 | ProductAmount |
| Client3 | ProductId1 | ProductAmount |
| Client3 | ProductId2 | ProductAmount |
| Client4 | ProductId1 | ProductAmount |
| Client4 | ProductId2 | ProductAmount |
| Client4 | ProductId3 | ProductAmount |
| Client4 | ProductId4 | ProductAmount |
So every Row have to have ClientID and IF product amount >0 then add it's productId and the amount. I think you got the point.
Trying different formulas and lost my total hope for ChatGPT as well... I would like to make sustainable solution which I can use like monthly. Solution should be created using Excel at the moment so I would like to have the formulas (maybe macro).
