kumara_faith
Well-known Member
- Joined
- Aug 19, 2006
- Messages
- 923
- Office Version
- 365
Hi,
I have the following table:
Table 1 has the raw data. Each receipt number has two type of invoices. However, there seems to be duplicates in invoice numbers. Example for receipt number ABC 123, invoice number JH11457 and invoice number KN44578 is repeated twice.
The correct end results is as per Table 2
I am not sure how to formularize this as I cant use a normal unique formula as the receipt number can be repeated twice since there can be different invoices for the same receipt number as per Table 2.
Is there a way to use formula to derive Table 2? Appreciate all the help.
I have the following table:
Book2 | |||||||||
---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | |||
3 | Table 1 | Table 2 | |||||||
4 | Receipt No | Invoice 1 | Invoice 2 | Receipt No | Invoice 1 | Invoice 2 | |||
5 | ABC123 | JH11457 | KN44578 | ABC123 | JH11457 | KN44578 | |||
6 | ABC123 | JH11457 | KN44578 | ABC123 | JH11458 | KN44579 | |||
7 | ABC123 | JH11458 | KN44579 | NNB456 | GF11457 | RE77895 | |||
8 | NNB456 | GF11457 | RE77895 | NNB456 | KL44789 | RE44778 | |||
9 | NNB456 | GF11457 | RE77895 | ||||||
10 | NNB456 | KL44789 | RE44778 | ||||||
Sheet1 |
Table 1 has the raw data. Each receipt number has two type of invoices. However, there seems to be duplicates in invoice numbers. Example for receipt number ABC 123, invoice number JH11457 and invoice number KN44578 is repeated twice.
The correct end results is as per Table 2
I am not sure how to formularize this as I cant use a normal unique formula as the receipt number can be repeated twice since there can be different invoices for the same receipt number as per Table 2.
Is there a way to use formula to derive Table 2? Appreciate all the help.