Match and Merge cell values

Screenshot_2.jpg
Hello Everyone
In the picture above,
k3 = Table Number
2116= Bill number
16a6ec24e49 = Unique Code .

Whenever the customer enters and touches the tablet, the unique code generates itself. In this picture the customer is at seat number K3 and 2116 is the FIRST ORDER he ordered by tapping Order in tablet.
See here is the problem , the bill number is only issued after the customer takes his first order but not after he checks in.
So How Do i make that bill number link with table number and unique code?

Attachment Files Note
Filter column D to K3

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Your requirement is not clear, at all. A sample workbook will help.

What exactly are you trying to do? What is the relevance of the section coloured in yellow?

I don't understand AT ALL what you want.

That attached workbook may contain some data, but it doesn't help clarify your requirement....

What does your required output look like?

Sorry for making it hard . Here I will try to understand.

In the screenshot
k3 = Table Number
2116= Bill number
16a6ec24e49 = Unique Code .

Whenever the customer enters and touches the tablet, the unique code generates itself. In this picture the customer is at seat number K3 and 2116 is the FIRST ORDER he ordered by tapping Order in tablet.See here is the problem , the bill number is only issued after the customer takes his first order but not after he checks in.So How Do i make that bill number link with table number and unique code?

You've only repeated yourself...


Try explaining in a different way. Try manually producing (mocking up) your required output, so we know what you're trying to achieve.

What tablet? How does the code generate itself? Where is the customer tapping ORDER?

None of these things (code, order button, etc.) are in your sample file, so how can we help?

Wait Let me explain to all of you. Forget everything about tablet and all.

In the screenshot
Column C = Table Number
Column D = Bill Number
Column E = ID

in the screenshot , you can clearly see that Table number and ID is there, but bill number can only be seen at 19th row.
Also note that the bill number 2116 have same Table number and same ID .
Now how can I index and match the bill number with Table number and ID?

What it is now

K3 16a6ec24e49
K3 16a6ec24e49
K3 16a6ec24e49
K3 16a6ec24e49
K3 16a6ec24e49
K3 16a6ec24e49
K3 2116 16a6ec24e49
K3 2116 16a6ec24e49

What I want is
K3 2116 16a6ec24e49
K3 2116 16a6ec24e49
K3 2116 16a6ec24e49
K3 2116 16a6ec24e49
K3 2116 16a6ec24e49
K3 2116 16a6ec24e49
K3 2116 16a6ec24e49
K3 2116 16a6ec24e49

How is this data getting there in the first place? You can have either a formula or a value in any one cell - not both.

I think you have told us the wrong columns, by the way.

No this is a raw data . It is a log of restaurant . No formula is used here. Pure raw.
All I want is to link those bill number with table number and ID

You can't have both raw data and a formula in the same cell. You can't do this with a formula. I think you are going to need VBA, unless you would be willing to look at adding a helper column.

You can use Power Query, to fill the Bill value for all matching ID.

Format your source data as a table - I've assumed your headers are:

Date | Time | Label | Seat | Bill | ID | Duration

Now use:

Please Login or Register  to view this content.

Olly - did you hard code this line:

Please Login or Register  to view this content.

Can you explain the FillDown and FillUp bits? I've used FillDown before, but not in combination with groups.

@Ali:

Yes, that's written, not done by GUI.

What we're doing is creating a partition (table) for each ID. Then we fill the Bill value, both up and down, before recombining the partitions into one table again.

Splitting the steps out:

Please Login or Register  to view this content.

But it's more efficient to do it in one step

Thanks - I can use this in one of my PQs. Very useful to see it split out into stages, though.