Populating fields to create a flat file

[Svey]

Good day,

I have a file that containes multiple recepient names per drugstore with a quantity and a reward code separated with a pipe. I need to create a flat file from the data to create an import file for our shipping software.

I have provided a sample. In the example. I have a unique drugstore with the up to 9 cosmeticians that have taken various online classes. The rewards codes (A-B-C-D) are preceded with the qty of reward they should get. I would like to be able to create a flat file that I will be able to import directly into our in house shipping program. It could also be a flat file created for a relational operation.

Thank you for your help,

Yves

[AliGW]

What you have shown in the workbook is NOT a traditional flat file database. In a normalised flat file DB, each row will contain ONE record only. Does the layout have to be what you have shown, therefore, or are you open to a properly normalised solution?

[Glenn Kennedy]

Why are Diep Vy's items all prefixed with a 2 (L21, etc) when there is no obvious reason (that I can see)??

[Svey]

Hi,

Yes Ali I am open to properly normalized solution.

Glenn, in the file supplied to me, the name of Diep Vy is getting those items multple times. I think the cosmeticians can take the same online class multiple times and that generates multiple orders for the same reward to be sent. I don't control the input website, I just get the data gathered. I need to unified all the rewards being sent to one package per drugstore to minimize cost and be greener.

Yves

[Glenn Kennedy]

Yes... I have just spotted that. It might mess up the approach I was taking, which was to split the item A's out into separate cells for each person...

[Glenn Kennedy]

I assume that this layout (purple and tan cells ONLY have been "done") is no use to you...

[Glenn Kennedy]

Is it possible to have numbers OTHER than 1 in these bits:

•1|A•1|A•1|B•1|B

[Svey]

Glenn,

At the moment, if the qty are different then 1, they would have been manually entered by the client for internal usage. I scan for them early in the job, pull them and treat them separately. So no, they qty will not differ from 1.

Yves

[Glenn Kennedy]

So... is the layout (tan and purple cells ONLY have been worked on... at Post 6) any use?

[Svey]

Glenn,

If the same items are listed twice (L21,M21) for 1 name, the 4 items enumeration will not be able to handle a reward counts higher than 4 (cell F6).

Yves

[Glenn Kennedy]

So... no it's no good???

[Glenn Kennedy]

I have got it working... maybe not in the simplest way... but it seems OK. Can you check the ORANGE cells by varying what's in the BLUE cells for Name 1 ONLY... to see if there are any bugs. I can then extend it to cover the other names.

[Svey]

Tx Glenn,

I have tried adding many mode instances of A-B-C-D for the first name. I works very well.

Yves

[Glenn Kennedy]

Away for some exercise. I'll finish it in acouple of hours.

[Glenn Kennedy]

Hopefully, that's it....

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

[Svey]

Good day,

Glenn, I had to chance to try your solution at length. If more recipients are added by the client, I managed to modify your solution successfully but if the number of items gifted gets larger, I did not manage to successfully modify the formula to get conclusive results. I have tried to modify the $A$11:$B:14 to reflect the new item codes (letter) and the item descriptions but I think I am missing something to make it work.

Yves

[Glenn Kennedy]

I was afraid you'd ask about that.... I am trying to persuade myself to go for a walk in the hills... I'll take a look to remind myself about this now... and will try to modify it later.

[Svey]

Tx Glenn, have a nice walk.

Yves

[Glenn Kennedy]

I started to get very confused... and simplified things a bit. The formula is now fully dynamic. It goes in N11 and is copied down. it spills into the remaining columns. The nomenclature was a tad confusing.

If you need to add more ITEMS (cols AC to AD), you need to change the formula bits in red appropriately.

if you need to add more Name/Areas, change the bits in green.

Other than that, the only thing that still requires manual alteration is the headers in I10 and to the right.

Play with it a bit before restoring the names as you had them before!!

=IFERROR(LET(
A,COUNTA($AD$2:$AD$7),
B,INT(SEQUENCE(,A*COUNTA($I$1:$M$1),1,1/A)),
C,INDEX($I2:$M2,B),
D,INDEX($I11:$M11,B),
E,1+MOD(SEQUENCE(,A*COUNTA($I$1:$M$1),1,1)-1,A),
F,"<X><Y>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C,D,""),"•","",1),"•","|"),"|","</Y><Y>")&"</Y></X>",
G,"//Y[position() mod 2 = 0 and not(preceding::*=.)]["&E&"]",
(LEN(C)-LEN(SUBSTITUTE(C,"•1|"&FILTERXML(F,G),"")))/4&"-"&VLOOKUP(FILTERXML(F,G),$AC$2:$AD$7,2,FALSE)),"")

[Glenn Kennedy]

Argghh. And the file!!

[Svey]

Hello Glenn,

I have played with the formula. It works very well when I switch for my original data. One exception, I don't understand the result at position AK11.

Tx.

Yves

[Svey]

I have found that the only 5 items instead of 6 are listed from AF11 to AJ15.

I have looked at the suggested changes (red and green) but it does not add the extra A4 Item 6 header and column.

Yves